Sql中常见的刁钻写法(心得记录)

1.

 

HAVING count(cid)=(SELECT count(cid) FROM SC WHERE sid='01')

2.

SELECT a.*,b.Cname FROM
(SELECT CId,
MAX(score) as 最高分,
MIN(score) as 最低分,
AVG(score) as 平均分,
COUNT(1)  as 选修人数,
SUM(case when score>=60 then 1 else 0 end) / COUNT(1) as 及格率,
SUM(case when score>=70 and score< 80 then 1 else 0 end) / COUNT(1) as 中等率,
SUM(case when score>=80 and score< 90 then 1 else 0 end) / COUNT(1) as 优良率,
SUM(case when score>=90 then 1 else 0 end) / COUNT(1) as 优秀率
FROM SC
GROUP BY CId) a left join Course b on a.CId=b.CId
ORDER BY 选修人数 DESC,CId ASC

 

select *,
sum(case when 0<=score and score <=60 then 1 else 0 end )/count(1) as '[0,60]',
sum(case when 60<score and score <=70 then 1 else 0 end )/count(1) as '[60,70]',
sum(case when 70<score and score <=85 then 1 else 0 end )/count(1) as '[70,85]',
sum(case when 85<score and score <=100 then 1 else 0 end )/count(1) as '[85,100]'
from SC 
group by cid

3. 

4.计算年龄

 

select *, year(NOW())-year(sage) as 年龄
from Student

5.

6.

 

7.