表结构:a,b,c,d,e 其中a,b,c均为主键,现在想查出主键重复的记录,有点难度,忘指教
------解决方案--------------------
主键不允许重复.主键必须是唯一的.
------解决方案--------------------
a,b,c都是主键？ 共同成为主键的话，应该不能有重复的吧。
您是想在已有数据的基础上，将这3列建为主键？
------解决方案--------------------
- SQL code
select *from table1 twhere (select count(* ) from table1 where a=t.a and b=t.b and c=t.c)>1
------解决方案--------------------
- SQL code
--这样?------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2009-12-26 11:07:54-- Version:-- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) -- May 3 2005 23:18:38 -- Copyright (c) 1988-2003 Microsoft Corporation-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([a] int,[b] int,[c] int,[d] int)insert [tb]select 1,2,3,4 union allselect 2,2,3,4 union allselect 3,2,3,4 union allselect 4,2,3,4 union allselect 5,1,2,3--------------开始查询--------------------------select * from [tb] t where exists(select 1 from tb where a<>t.a and b=t.b and c=t.c and d=t.d)or exists(select 1 from tb where a=t.a and b<>t.b and c=t.c and d=t.d)or exists(select 1 from tb where a=t.a and b=t.b and c<>t.c and d=t.d)or exists(select 1 from tb where a=t.a and b=t.b and c=t.c and d<>t.d)----------------结果----------------------------/* a b c d ----------- ----------- ----------- ----------- 1 2 3 42 2 3 43 2 3 44 2 3 4（所影响的行数为 4 行）*/
------解决方案--------------------
- SQL code
select * from [tb]where ltrim(a)+ltrim(b)+ltrim(c)in(select ltrim(a)+ltrim(b)+ltrim(c)from [tb]group by ltrim(a)+ltrim(b)+ltrim(c)having count(1) >= 2)
------解决方案--------------------
哦 只有a,b,c那这样.
- SQL code
select * from [tb] t where exists(select 1 from tb where a<>t.a and b=t.b and c=t.c and d=t.d)or exists(select 1 from tb where a=t.a and b<>t.b and c=t.c and d=t.d)or exists(select 1 from tb where a=t.a and b=t.b and c<>t.c and d=t.d)
------解决方案--------------------
- SQL code
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2009-12-26 11:07:54-- Version:-- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) -- May 3 2005 23:18:38 -- Copyright (c) 1988-2003 Microsoft Corporation-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([a] int,[b] int,[c] int,[d] int)insert [tb]select 1,2,3,4 union allselect 2,2,3,4 union allselect 3,2,3,4 union allselect 4,2,3,4 union allselect 5,1,2,3--------------开始查询--------------------------select * from [tb] t where exists(select 1 from tb where ltrim(a)+ltrim(b)+ltrim(c)<>ltrim(t.a)+ltrim(t.b)+ltrim(t.c) and d=t.d)----------------结果----------------------------/* a b c d ----------- ----------- ----------- ----------- 1 2 3 42 2 3 43 2 3 44 2 3 4（所影响的行数为 4 行）*/