[Quote=引用 8 楼 youshang444 的回复:]
SQL code
select name, sum(case when score>=60 then 1 else 0 end) as jigekemu,sum(case when score>=60 then sscore else 0 end) as jigezongfen from student group by name;
[/Quote]
select name, sum(case when score>=60 then 1 else 0 end) as jigekemu,sum(case when score>=60 then sscore else 0 end) as jigezongfen from student group by name;
SELECT NAME,COUNT(COURSE),SUM(SCORE) FROM (SELECT * FROM SCORE WHERE SCORE>=60)
GROUP BY NAME
UNION ALL
SELECT DISTINCT NAME,CASE WHEN SCORE <60 THEN 0 END,CASE WHEN SCORE <60 THEN 0 END FROM
(
SELECT NAME,COURSE,SCORE FROM SCORE WHERE SCORE<60 AND NAME NOT IN
(SELECT NAME FROM SCORE WHERE SCORE>=60))
[Quote=引用 3 楼 keiy 的回复:]
没考虑效率,结果也不是你的顺序(这个,可以把每个名字用一ID表示,一般设计表时必须的)
select name,sum(m_total) ,sum(m_count) from (
select name,sum(score) m_total ,count(*) m_count from STUDENT where score >=60 group by name
union
select ……
[/Quote]
没考虑效率,结果也不是你的顺序(这个,可以把每个名字用一ID表示,一般设计表时必须的)
select name,sum(m_total) ,sum(m_count) from (
select name,sum(score) m_total ,count(*) m_count from STUDENT where score >=60 group by name
union
select name,0,0 from STUDENT where score <60 group by name
) group by name
[Quote=引用 1 楼 im110 的回复:]
select a.name, count(a.score) as kemu, sum(a.score) as total from (select name, score from student where score > 60) as a;
[/Quote]
错的!!!!!!
一 select name, score from student where score > 60 王五没了!
二 没分组
还不如不回复!!!