select m.* from sc m , sc n where m.cno = '001' and n.cno = '002' and m.grade > n.grade
select m.* , n.* from sc m , sc n where m.cno = '001' and n.cno = '002' and m.grade > n.grade
select * from sc a where cno='001' and not exists(select * from sc where grade>a.grade and cno='002')这样?
select m.* , n.* from sc m , sc n where m.cno = '001' and n.cno = '002' and m.grade > n.grade and m.sno = n.sno
select * from ( select sno,grade from sc where cno='001') a join ( select sno,grade from sc where cno='002' ) b on a.sno=b.sno where a.grade>b.grade
select m.* , n.* from sc m , sc n where m.cno = '001' and n.cno = '002' and m.grade > n.grade 顶
CREATE TABLE #A (ID INT,S_NO VARCHAR(10),NUM INT )INSERT INTO #A SELECT 201, 'A' , 80 INSERT INTO #A SELECT 201, 'B', 60 INSERT INTO #A SELECT 201, 'C' , 75 INSERT INTO #A SELECT 202, 'A', 60 INSERT INTO #A SELECT 202, 'B' , 75 INSERT INTO #A SELECT 202, 'C', 85SELECT * FROM #A A WHERE NUM>(SELECT NUM FROM #A WHERE ID=A.ID AND S_NO='B') AND S_NO='A'DROP TABLE #A
from sc a
where cno='001'
and not exists(select * from sc where grade>a.grade and cno='002')这样?
( select sno,grade from sc where cno='001') a join
( select sno,grade from sc where cno='002' ) b
on a.sno=b.sno
where a.grade>b.grade
顶
INSERT INTO #A SELECT 201, 'B', 60
INSERT INTO #A SELECT 201, 'C' , 75
INSERT INTO #A SELECT 202, 'A', 60
INSERT INTO #A SELECT 202, 'B' , 75
INSERT INTO #A SELECT 202, 'C', 85SELECT * FROM #A A
WHERE NUM>(SELECT NUM FROM #A WHERE ID=A.ID AND S_NO='B')
AND S_NO='A'DROP TABLE #A