mysql统计查询语句_mysql查询语句举例

select stuid, name, sum(score) as sum_score from stuscore group by stuid order by sum_score desc;

select stuid, name, sub, score from stuscore where score in (select max(score) from stuscore group by stuid);

select stuid, name, sub, score from stuscore where score in (select max(score) from stuscore group by sub);

select a.* from stuscore a where exists (select count(*) from stuscore where sub = a.sub and score > a.score having count(*) < 2) order by a.sub, a.score desc;

select sub, avg(score) as avg_score from stuscore group by sub order by avg_score desc;

select (select (count(stuid)+1 from (select stuid, sum(score) as sum_score from stuscore group by stuid) as A where A.sum_score > B.sum_score) as seq, B.stuid, B.name, B.sum_score from (select stuid, name, sum(score) as sum_score from stuscore group by stuid)as B order by sum_score desc;

select stuid, name, score, sub from stuscore where sub = 'math' order by score desc limit 1, 3;

select (select (count(stuid)+1 from (select stuid, score from stuscore where sub = 'math') as A where A.score > B.score) as seq, B.stuid, B.name, B.sum_score from (select stuid, name, sub, score from stuscore where sub = 'math' and name = '李四')as B;

select stuid, name, sum(case when sub = 'chinese' then score else 0 end) as chinese, sum(case when sub = 'math' then score else 0 end) as math, sum(case when sub = 'english' then score else 0 end) as english, sum(score) as sum_score, avg(score) as avg_score from stuscore group by stuid;

select sub, sum(case when score < 60 then 1 else 0 end) as lower_60, sum(case when score < 81 and score > 59 then 1 else 0 end) as between_60_80, sum(case when score > 80 then 1 else 0 end) as higher_80 from stuscore group by sub;