select a.sno,a.grade as '1号课成绩',b.grade as '2号课成绩' from (select sno,grade from 表 where cno ='1号课')a inner join (select sno,grade from 表 where cno ='2号课')b on a.sno=b.sno where a.grade >b.grade
DECLARE @T TABLE(SNO INT,CNO INT,GRADE INT) INSERT @T SELECT 1,1,80 UNION ALL SELECT 1,2,90 UNION ALL SELECT 2,1,100 UNION ALL SELECT 2,2,90 --test SELECT SNO, '1号成绩'=SUM(CASE WHEN CNO=1 THEN GRADE ELSE 0 END), '2号成绩'=SUM(CASE WHEN CNO=2 THEN GRADE ELSE 0 END) FROM @T A GROUP BY SNO HAVING SUM( CASE WHEN CNO=1 THEN GRADE ELSE -GRADE END)>1
create table test1 (sno varchar(3), cno int, grade float)insert into test1(sno,cno,grade) values('001',1,90) insert into test1(sno,cno,grade) values('001',2,80) insert into test1(sno,cno,grade) values('002',1,80) insert into test1(sno,cno,grade) values('002',2,90)select a.sno,a.grade as 1号成绩,b.grade as 2号成绩 from (select sno,grade from test1 where cno=1) a, (select sno,grade from test1 where cno=2) b where a.sno=b.sno and a.grade>b.grade
INSERT @T SELECT 1,1,80
UNION ALL SELECT 1,2,90
UNION ALL SELECT 2,1,100
UNION ALL SELECT 2,2,90
--test
SELECT
SNO,
'1号成绩'=SUM(CASE WHEN CNO=1 THEN GRADE ELSE 0 END),
'2号成绩'=SUM(CASE WHEN CNO=2 THEN GRADE ELSE 0 END)
FROM @T A
GROUP BY SNO
HAVING SUM( CASE WHEN CNO=1 THEN GRADE ELSE -GRADE END)>1
(sno varchar(3),
cno int,
grade float)insert into test1(sno,cno,grade)
values('001',1,90)
insert into test1(sno,cno,grade)
values('001',2,80)
insert into test1(sno,cno,grade)
values('002',1,80)
insert into test1(sno,cno,grade)
values('002',2,90)select a.sno,a.grade as 1号成绩,b.grade as 2号成绩
from (select sno,grade from test1 where cno=1) a,
(select sno,grade from test1 where cno=2) b
where a.sno=b.sno and a.grade>b.grade