mysql复杂查询需求总结

问题一：分组后将两条记录合并、列值变成字段

(case when then else end)  as  和group by连用

栗子：

id, name, type, value
'1', '张三', 'height', '180'
'2', '张三', 'weight', '72'
'3', '李四', 'height', '175'
'4', '李四', 'weight', '62'

想将其变为

name, height, weight
'张三', '180', '72'
'李四', '175', '62'

sql语句为：

select name,
MAX(case type when 'height' then value else 0 end) as 'height',
MAX(case type when 'weight' then value else 0 end) as 'weight'
from test.user
group by name

问题二：选择每组中值最大的

max()

以上面例子为例
由name分组后，每组会有两条数据，而这两条数据都会去执行语句中的两个case。
当第一条数据走到第一个case,为true则值为180，经过第二个case时，为false,值为0；
当第二条数据走到第一个case,为false,值为0，经过第二个case时，为true，值为72。
两条数据，第一次的结果（180,0）对应height,第二次的结果（0,72）对应weight，
（因为分组聚合的含义是，将相同组内的多条记录，按照某个规则，只能输出唯一一条记录，一旦允许多条就会出错）
所以使用max()选择最大的一个(字符和数组同样有效)

问题三：每组中选择符合条件的记录

(case 1
when 2 then 3 
when 4 then 5
else 6 end)  as 7

栗子：

id, name, type, value
'1', '张三', 'height', '180'
'3', '张三', 'sex', 'man'
'5', '李四', 'weight', '62'
'6', '李四', 'sex', 'man'

如果想看张三的height,和了李四的weight

name, type
'张三', 'height'
'李四', 'weight'

sql语句为：

select name,
MAX(
case type 
when 'height' then type
when 'weight' then type
else 0 end) as 'type'
from test.user
group by name

问题四：分组排序

row_number() over (partition by 1 order by 2) as rk

该语法仅适用于mysql8.0及以上版本
栗子：

id, class, name, subject, score
'1', '1', '小明', '语文', '89'
'2', '1', '小明', '数学', '98'
'3', '1', '小刚', '语文', '75'
'4', '1', '小刚', '数学', '100'
'5', '2', '小红', '语文', '100'
'6', '2', '小红', '数学', '90'

想要查看每个学生自己每科的成绩在自己所有学科中的排名

name, subject, score, rk
'小刚', '数学', '100', '1'
'小刚', '语文', '75', '2'
'小明', '数学', '98', '1'
'小明', '语文', '89', '2'
'小红', '语文', '100', '1'
'小红', '数学', '90', '2'

sql语句

select name, subject, score, 
row_number() over (partition by name order by score desc) as rk
from test.student

问题五：字符串时间与date时间比较

将date时间转为字符串，再比较

date_format(now(), "%Y-%m-%d %H:%M:%S")

栗子：

id, name, type, value, create_at
'3', '张三', 'effectTime', '2020-11-02 12:11:00', '2020-11-03 07:52:07'
'6', '李四', 'effectTime', '2020-11-04 12:11:00', '2020-11-03 07:52:07'

value属性为字符串
create_at属性为TIMESTAMP
查看生效期，小于创建时间的记录

name, type, value
'张三', 'effectTime', '2020-11-02 12:11:00'

sql语句

select name, type, value
from test.user
where value <= date_format(create_at, '%Y-%m-%d %H:%M:%s')

问题六：分段统计

栗子：

id, name, amount
'1', 'a', '25'
'2', 'b', '45'
'3', 'c', '67'
'4', 'd', '123'
'5', 'e', '100'
'6', 'f', '150'
'7', 'g', '15'
'8', 'h', '26'
'9', 'i', '10'

想要根据0-25,25-50,50-100,100以上分段统计各区间项目数量

区间, 数量
'[25, 50)', '3'
'[50, 100)', '1'
'[100, 以上）', '3'
'[0, 25)', '2'

sql语句为

select 
case 
when amount >=0 and amount < 25 THEN '[0, 25)'
when amount >=25 and amount < 50 THEN '[25, 50)'
when amount >=50 and amount < 100 THEN '[50, 100)'
else '[100, 以上）'
end as '区间',
count(name) as 数量
from test.project
group by
case
when amount >=0 and amount < 25 THEN '[0, 25)'
when amount >=25 and amount < 50 THEN '[25, 50)'
when amount >=50 and amount < 100 THEN '[50, 100)'
else '[100, 以上）'
end

本文地址：https://blog.csdn.net/qq_33017507/article/details/109468859