總結的sql常用經典查詢

1.表名為tab_1，其中有一個欄位為code，寫一條SELECT按code從小到大排序的前10條記錄並按code從大到小排序（選所有欄位）
select * from (select top 10 * from employee order by bh) b order by bh desc

2.zyh fyxm zjje
831 1 90
831 5 88
831 6 99
832 1 20
832 2 30
832 5 60
怎麼改成:
zyh fyxm1 fyxm2 fyxm3 fyxm4 fyxm5 fyxm6 zjje
831 90 88 99
832 20 30 60
格式


實現語句

SELECT zyh,
max(CASE WHEN fyxm=1 THEN zjje ELSE 0 END) fyxm1,
max(CASE WHEN fyxm=2 THEN zjje ELSE 0 END) fyxm2,
max(CASE WHEN fyxm=3 THEN zjje ELSE 0 END) fyxm3,
max(CASE WHEN fyxm=4 THEN zjje ELSE 0 END) fyxm4,
max(CASE WHEN fyxm=5 THEN zjje ELSE 0 END) fyxm5,
max(CASE WHEN fyxm=6 THEN zjje ELSE 0 END) fyxm6
FROM tb_name
GROUP BY zyh

3一條SQL語句刪除重復記錄

select * from employee a ,( select xm,max(bh) as 'bh' from employee group by xm having count(*)>1) b
where a.bh=b.bh

select * from employee a
where exists(select 1 from employee where a.xm=employee.xm and a.bh<employee.bh)
4.--交叉表實現功能

select bm,

( case when xb= '男' then count(*) else 0 end ) '男',
( case when xb= '女' then count(*) else 0 end ) '女'
from employee
group by bm,xb

5.找出最小未使用的整數
table1
ID
1
2
3
6
9
結果為:4

select min(fid) from
(select fid=(select count(1) from table1 where id<=a.id) from table1 a) t
where t.fid not in(selec id from table1)