select * from (select distinct * ,sum(1) as 数量 from 表 group by 所有字段) a where a.数量>1
select * from (select * ,sum(1) as 数量 from 表 group by 所有字段) a where a.数量>1
这样查找,数据仍然合不拢,我用select distinct * from house得到840条,用你的哪句得到112条,总数据为955条,少了3条
select * from 表 minus select distinct * from 表
to zjcxc(邹建) 能否写出语句,多谢
用 NOT IN 语句,行不行?
-- 用临时表实现: SELECT IDENTITY(INT,1,1) ID,* INTO #tmp FROM TSELECT * FROM #tmp WHERE ID NOT IN (SELECT MAX(ID) FROM #tmp GROUP BY COL1,COL2,COL3...)DROP TABLE #tmp -- 注意GROUP BY后是你原来表中的有字段
你提的问题不好!T(f1,f2,f3,f4) a,a,a,a a,a,a,a a,a,a,a b,b,b,b b,b,b,b c,c,c,c c,c,c,c c,c,c,c c,c,c,c这样的表 你说重复的数据是 9 ? 还是 3 ? 还是 6 ?只能说 形如: a,a,a,a b,b,b,b c,c,c,c 有重复!所以: select f1,f2,f3,f4 from T group by f1,f2,f3,f4 having count(*) > 1select * from T a where (select count(*) from T where f1 = a.f1 and f2 = a.f2 and f3 = a.f3 and f4 = a.f4) >1
minus
select distinct * from 表
SELECT IDENTITY(INT,1,1) ID,* INTO #tmp FROM TSELECT *
FROM #tmp
WHERE ID NOT IN
(SELECT MAX(ID) FROM #tmp GROUP BY COL1,COL2,COL3...)DROP TABLE #tmp
-- 注意GROUP BY后是你原来表中的有字段
a,a,a,a
a,a,a,a
a,a,a,a
b,b,b,b
b,b,b,b
c,c,c,c
c,c,c,c
c,c,c,c
c,c,c,c这样的表
你说重复的数据是 9 ? 还是 3 ? 还是 6 ?只能说 形如:
a,a,a,a
b,b,b,b
c,c,c,c
有重复!所以:
select f1,f2,f3,f4
from T
group by f1,f2,f3,f4
having count(*) > 1select *
from T a
where (select count(*) from T where f1 = a.f1 and f2 = a.f2 and f3 = a.f3 and f4 = a.f4) >1
所有字段都没有唯一值的