select * from tb t where exists(select 1 from tb where dw=t.dw and id<>t.id)
/*------------------------------------------------------------------ -- Author : htl258(Tony) -- Date : 2010-04-12 08:16:56 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/ --> 生成测试数据表:taIF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta] GO CREATE TABLE [ta]([id] INT,[dw] NVARCHAR(10)) INSERT [ta] SELECT 1,'AAAAA' UNION ALL SELECT 2,'BB' UNION ALL SELECT 3,'AAAAA' UNION ALL SELECT 4,'CCS' UNION ALL SELECT 5,'BB' UNION ALL SELECT 6,'CCS' UNION ALL SELECT 7,'BB' GO --SELECT * FROM [ta]-->SQL查询如下: select * from ta t where exists(select 1 from ta where dw=t.dw and id<>t.id) --order by 2 /* id dw ----------- ---------- 1 AAAAA 3 AAAAA 2 BB 5 BB 7 BB 6 CCS 4 CCS(7 行受影响) */
select * from tb where id in (select id from tb group by dw having count(*) > 1)
IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta] GO CREATE TABLE [ta]([id] INT,[dw] NVARCHAR(10)) INSERT [ta] SELECT 1,'AAAAA' UNION ALL SELECT 2,'BB' UNION ALL SELECT 3,'AAAAA' UNION ALL SELECT 4,'CCS' UNION ALL SELECT 5,'BB' UNION ALL SELECT 6,'CCS' UNION ALL SELECT 7,'BB' GO select * from ta where [dw] in (select [dw] from ta group by [dw] having count(*) > 1) order by dw /* id dw ----------- ---------- 1 AAAAA 3 AAAAA 2 BB 5 BB 7 BB 6 CCS 4 CCS(7 行受影响)*/
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([id] int,[dw] varchar(5)) insert [TB] select 1,'AAAAA' union all select 2,'BB' union all select 3,'AAAAA' union all select 4,'CCS' union all select 5,'BB' union all select 6,'CCS' union all select 7,'BB' UNION ALL SELECT 8,'DD'select * from [TB] SELECT * FROM dbo.tb WHERE dw IN (SELECT dw FROM tb GROUP BY dw HAVING COUNT(*)>1) ORDER BY dw/* id dw 1 AAAAA 3 AAAAA 2 BB 5 BB 7 BB 6 CCS 4 CCS
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-12 08:16:56
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:taIF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
GO
CREATE TABLE [ta]([id] INT,[dw] NVARCHAR(10))
INSERT [ta]
SELECT 1,'AAAAA' UNION ALL
SELECT 2,'BB' UNION ALL
SELECT 3,'AAAAA' UNION ALL
SELECT 4,'CCS' UNION ALL
SELECT 5,'BB' UNION ALL
SELECT 6,'CCS' UNION ALL
SELECT 7,'BB'
GO
--SELECT * FROM [ta]-->SQL查询如下:
select * from ta t where exists(select 1 from ta where dw=t.dw and id<>t.id) --order by 2
/*
id dw
----------- ----------
1 AAAAA
3 AAAAA
2 BB
5 BB
7 BB
6 CCS
4 CCS(7 行受影响)
*/
where id in (select id from tb group by dw having count(*) > 1)
DROP TABLE [ta]
GO
CREATE TABLE [ta]([id] INT,[dw] NVARCHAR(10))
INSERT [ta]
SELECT 1,'AAAAA' UNION ALL
SELECT 2,'BB' UNION ALL
SELECT 3,'AAAAA' UNION ALL
SELECT 4,'CCS' UNION ALL
SELECT 5,'BB' UNION ALL
SELECT 6,'CCS' UNION ALL
SELECT 7,'BB'
GO
select * from ta
where [dw] in (select [dw] from ta group by [dw] having count(*) > 1)
order by dw
/*
id dw
----------- ----------
1 AAAAA
3 AAAAA
2 BB
5 BB
7 BB
6 CCS
4 CCS(7 行受影响)*/
if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[dw] varchar(5))
insert [TB]
select 1,'AAAAA' union all
select 2,'BB' union all
select 3,'AAAAA' union all
select 4,'CCS' union all
select 5,'BB' union all
select 6,'CCS' union all
select 7,'BB' UNION ALL
SELECT 8,'DD'select * from [TB]
SELECT * FROM dbo.tb
WHERE dw IN (SELECT dw FROM tb GROUP BY dw HAVING COUNT(*)>1)
ORDER BY dw/*
id dw
1 AAAAA
3 AAAAA
2 BB
5 BB
7 BB
6 CCS
4 CCS