牛客404425958号

分类

/ 注册

全部文章（共214篇）

题解 | 统计salary的累计和running_total

select emp_no, salary, sum(salary) over(order by emp_no) as running_total from salaries where to_date='9999-01-01' 不需要分组，不写partition by

2024-10-26

0 39

题解 | #获取有奖金的员工相关信息。#

select emp_no, first_name, last_name, btype, salary, case when btype=1 then round(salary*0.1,1) when btype=2 then round(s...

2024-10-26

0 57

关键字exists查找未分配具体部门的员工的所有信息

/* SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition); */ select * from employees where not exis...

2024-10-26

0 59

limit 分页查询每5行一页，返回第2页的数据

/* limit 当前页*页大小, 页大小 */ select * from employees order by emp_no limit 5,5;

2024-10-26

0 59

题解 | #去掉最值的平均工资#

select avg(salary) avg_salary from salaries where to_date='9999-01-01' and salary not in((select max(salary) from salaries where to_date='9999-01-01')...

2024-10-26

0 52

分组拼接题解 | #按照dept_no进行汇总#

/*分组拼接：group_concat(拼接列 order by 排序列 asc/desc separator '连接符') 与group by搭配使用 */ select dept_no, group_concat(emp_no order by emp_no separator ',') e...

2024-10-26

0 45

length replace regexp提取字母数字

/* 1、将逗号替换成空白，string长度减去新字段长度，就是逗号个数。 select id, length(string)-length(replace(string,',','')) as cnt from strings 2、regexp_replace()提取字段中的字母数字，计算长度差...

2024-10-26

0 66

题解 | #获取employees中的first_name#

select first_name from employees order by right(first_name,2),right(first_name,1)

2024-10-26

0 43

题解 | #查找字符串中逗号出现的次数#

/*将逗号替换成空白，string长度减去新字段长度，就是逗号个数。 */ select id, length(string)-length(replace(string,',','')) as cnt from strings

2024-10-26

0 63

将last_name和first_name通过(')连接起来

/*单引号可以用双引号括起来 */ select concat(last_name,"'",first_name) from employees

2024-10-26

0 69