SQL语句扩展

SQL语句扩展

1.查询结果添加虚拟列

​ case … when … then … else … end

​ 通过 case 实现，case 后面加作为条件判断的字段， when 后面跟具体的条件，条件成立字段值为 then 后面的值，条件不成立字段值为 else 后面的值。

​ 例：在查询emp表时，基于工资进行判断，查询结果添加虚拟列 state，工资大于等于3000的员工 state 为 high ，工资低于3000的员工 state 为 low，最后通过别名给虚拟列起名为 state。

select name,sal,(case sal when sal>=3000 then 'high' else 'low' end) state from emp;

查询结果如下：

name	sal	state
yyf	3100	high
longdd	2000	low
laochen	1300	low

​ 其中state为虚拟列，临时添加的一列，在emp表中本身并没有此列

2.删除多字段组合重复记录

​ 例如，要求 emp 表中只能保存 name + sal 这两个字段组合唯一的记录，如果存在 name + sal 相同的多个字段，那么要通过 delete语句进行删除。

delete from emp where (name,sal) in (select name,sal from emp group by name,sal having count(*) > 1);

3.删除表中单一字段重复记录，并保留id最小的记录

​ 例如，删除 emp 表中 name 重复的记录，只保留 id 最小的记录。

delete from emp where name in (select name from emp group by name having count(name)>1) and id not in (select min(id) from emp group by name having count(name)>1);

4.查询某一字段重复的所有数据

​ 例如，目前在 emp 表中需要查询 name 重复的所有记录进行显示。

select id,name,sal from emp where name in (select name from emp group by name having count(name) >1);

5.MySQL行转列

​ 查询 tb_score 表：

id	user_name	course	score
1	张三	数学	34
2	张三	语文	35
3	张三	英语	36
4	李四	数学	88
5	李四	语文	89
6	李四	英语	90
7	王五	数学	60
8	王五	语文	61
9	王五	英语	62

​ 查询结果：

username	数学	语文	英语
张三	34	35	36
李四	88	89	90
王五	60	61	62

​ SQL语句编写：

​ 实现思路1：使用 case…when…then…else…end

select username,
	sum(case course when '语文' then score else 0 end) '语文',
	sum(case course when '数学' then score else 0 end) '数学',
	sum(case course when '英语' then score else 0 end) '英语'
from tb_score 
group by username;

​ 实现思路2：使用 if() 进行行转列

select username,
	sum(if(course='语文',score,0)) '语文',
	sum(if(course='数学',score,0)) '数学',
	sum(if(course='英语',score,0)) '英语'
from tb_score
group by username;