日期:2014-05-17  浏览次数:20459 次

多主键重复记录查询
表结构:a,b,c,d,e 其中a,b,c均为主键,现在想查出主键重复的记录,有点难度,忘指教

------解决方案--------------------
主键不允许重复.主键必须是唯一的.
------解决方案--------------------
a,b,c都是主键? 共同成为主键的话,应该不能有重复的吧。 

您是想在已有数据的基础上,将这3列建为主键?

------解决方案--------------------
SQL code
select *
from table1 t
where (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 all
select 2,2,3,4 union all
select 3,2,3,4 union all
select 4,2,3,4 union all
select 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           4
2           2           3           4
3           2           3           4
4           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 all
select 2,2,3,4 union all
select 3,2,3,4 union all
select 4,2,3,4 union all
select 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           4
2           2           3           4
3           2           3           4
4           2           3           4

(所影响的行数为 4 行)
*/