查询每个科目TOP N条数据 疑问?????????????????

[王继波]

create table sc(
sno varchar(10),
cno varchar(10),
score float(3,1)
);

insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');

=================================sql 1 =============================================
SELECT s1.* FROM SC s1 WHERE (SELECT count(s2.score) FROM sc s2 where s1.cno = s2.cno AND s2.score >= s1.score) <=2 ;


==================================sql 2============================================
SELECT s1.* FROM SC s1 WHERE (SELECT count(s2.score) FROM sc s2 where s1.cno = s2.cno AND s2.score > s1.score) <=2 ;


s002 c001 80.9
s002 c002 72.9
================================== 疑问 ============================================
把=号去掉了, 为什么会显示这两条数据? 我理解这条sql 是显示每条时把 外查询s1 的参数带子查询, 判断是否符合条件, 如果符合就显示.不符合就不显示. 这两条明显不符合where后的条件呀 ?
WHERE (SELECT count(s2.score) FROM sc s2 where s2.cno = 'c001' AND s2.score > '80.9') <=2 ;
WHERE (SELECT count(s2.score) FROM sc s2 where s2.cno = 'c002' AND s2.score > '72.9') <=2 ;

[VertigozZ]

别套子查询了，直接这样就可以了：

取每个课程的前两名（cno为课程编号）


SELECT t1.sno,t1.cno,t1.score
FROM sc t1 LEFT JOIN sc t2 ON t1.cno = t2.cno
WHERE t1.score <= t2.score 
GROUP BY t1.sno,t1.cno,t1.score HAVING count(1) <= 2
order BY t1.cno ASC,t1.score desc

[zjcxc]

排序一下数据，得到的结果，> 的结果是编号从0开始，所以 <=2 的结果是 top 3 条，结果是对的嘛 s001 c001 78.9 s001 c002 82.9 s001 c003 59.0 s002 c001 80.9 s002 c002 72.9 s003 c001 81.9 s003 c002 81.9 s004 c001 60.9

[zjcxc]

排序贴的数据 copy 错了，是这个 s003 c001 81.9 s002 c001 80.9 s001 c001 78.9 s004 c001 60.9 s001 c002 82.9 s003 c002 81.9 s002 c002 72.9 s001 c003 59.0

[VertigozZ]

再来分析一下你的sql2
结果集如下：



最终结果集也就是你所得到的7个。

sql2 如果想要得到正确的结果 把最后的等号去掉即可

SELECT s1.* FROM SC s1 WHERE (SELECT count(s2.score) FROM sc s2 where s1.cno = s2.cno AND s2.score > s1.score) <2