求某表中有两例值完全相同的所有数据行例如表: XTABLE CODE NAME SPEC
2002 A货品 1*1
2003 A货品 1*1
2004 B货品 2*123求结果:
显示所有列值NAME和SPEC内容相同时的记录 CODE NAME SPEC
2002 A货品 1*1
2003 A货品 1*1
2002 A货品 1*1
2003 A货品 1*1
2004 B货品 2*123求结果:
显示所有列值NAME和SPEC内容相同时的记录 CODE NAME SPEC
2002 A货品 1*1
2003 A货品 1*1
where exists(select * from xtable
where name=x.name and spec=x.spec
and code <> x.code)
where exists (select 1 where t.code<>code and t.name=name and t.spec=spec)
where exists (select 1 from tb where t.code <>code and t.name=name and t.spec=spec)
select * from XTABLE a,
(select NAME,SPEC,count(1) from XTABLE group byNAME,SPEC having count(1)>1) as b
where a.NAME=b.NAME and a.SPEC=b.SPEC
select a.* from XTABLE a,
(select NAME,SPEC,count(1) from XTABLE group byNAME,SPEC having count(1)>1) as b
where a.NAME=b.NAME and a.SPEC=b.SPEC
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-29 14:42:27
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([CODE] int,[NAME] varchar(5),[SPEC] varchar(5))
insert [tb]
select 2002,'A货品','1*1' union all
select 2003,'A货品','1*1' union all
select 2004,'B货品','2*123'
--------------开始查询--------------------------
select
*
from
tb t
where
exists (select 1 from tb where t.code <>code and t.name=name and t.spec=spec)
----------------结果----------------------------
/* CODE NAME SPEC
----------- ----- -----
2002 A货品 1*1
2003 A货品 1*1(2 行受影响)
*/
from XTABLE a,
(select NAME,SPEC FROM XTABLE GROUP BY NAME,SPEC HAVING COUNT(1)>0) b
where
a.NAME=b.NAME and a.SPEC=b.SPEC
WHERE EXISTS(SELECT 1 FROM [tb] WHERE A.NAME=NAME AND A.SPEC=SPEC AND A.CODE<>CODE)