--建立环境 create table T(scoid int, stid int, score int)insert T select 1,1, 40 union all select 2,1, 50 union all select 3,1, 60 union all select 4,2, 40 union all select 5,2, 51 union all select 6,2, 60 union all select 7,3, 40 union all select 8,3, 52 union all select 9,3, 60--查询代码 select a.* from t a ,(select score from t group by score having count(1)>1) b where a.score=b.score
要求:如何返回下表中score字段值的所有重复数据。 标准答案RU如下: ------ SELECT * FROM score WHERE score in (SELECT score FROM score GROUP BY score HAVING COUNT(*)>1) ORDER BY score
create table T(scoid int, stid int, score int)insert T select 1,1, 40
union all select 2,1, 50
union all select 3,1, 60
union all select 4,2, 40
union all select 5,2, 51
union all select 6,2, 60
union all select 7,3, 40
union all select 8,3, 52
union all select 9,3, 60--查询代码
select a.*
from t a ,(select score from t group by score having count(1)>1) b
where a.score=b.score
----------- ----------- -----------
1 1 40
4 2 40
7 3 40
6 2 60
3 1 60
9 3 60(所影响的行数为 6 行)
标准答案RU如下:
------
SELECT *
FROM score
WHERE score in (SELECT score FROM score GROUP BY score HAVING COUNT(*)>1)
ORDER BY score