这样行么?select * from [thetable] a where a.[ID]=2007 and exists (select 1 from [thetable] b where b.[ID]=2008 and a.[Name]=b.[Name]
SELECT * FROM A WHERE ID = 2007 INTERSECT SELECT * FROM A WHERE ID = 2008
SELECT a.* FROM [tb] a JOIN ( SELECT [name] FROM [tb] WHERE [id] IN('a','b') GROUP BY [name] HAVING COUNT( DISTINCT [id]) >= 2 )b ON a.[name]=b.[name]
--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT,[name] VARCHAR(10)) INSERT [tb] SELECT 2006,'a' UNION ALL SELECT 2006,'b' UNION ALL SELECT 2006,'c' UNION ALL SELECT 2007,'aa' UNION ALL SELECT 2007,'b' UNION ALL SELECT 2007,'c' UNION ALL SELECT 2008,'a' UNION ALL SELECT 2008,'b' UNION ALL SELECT 2008,'cc' --------------开始查询--------------------------SELECT a.* FROM [tb] a JOIN ( SELECT [name] FROM [tb] WHERE [id] IN(2007,2008) GROUP BY [name] HAVING COUNT( DISTINCT [id]) >= 2 )b ON a.[name]=b.[name] ----------------结果---------------------------- /* id name ----------- ---------- 2007 b 2006 b 2008 b(3 行受影响) */
SELECT a.* FROM [tb] a JOIN ( SELECT [name] FROM [tb] WHERE [id] IN(2007,2008) GROUP BY [name] HAVING COUNT( DISTINCT [id]) >= 2 )b ON a.[name]=b.[name] [color=#FF0000]AND a.[id] IN(2007,2008) [/color 少加一个条件
no qq no msn no tel 看其他人能不能帮你了
发现最近回帖总被人忽略--> 测试数据:[A] IF OBJECT_ID('[A]') IS NOT NULL DROP TABLE A GO CREATE TABLE A([id] INT,[name] VARCHAR(10)) INSERT A SELECT 2006,'a' UNION ALL SELECT 2006,'b' UNION ALL SELECT 2006,'c' UNION ALL SELECT 2007,'aa' UNION ALL SELECT 2007,'b' UNION ALL SELECT 2007,'c' UNION ALL SELECT 2008,'a' UNION ALL SELECT 2008,'b' UNION ALL SELECT 2008,'cc' --------------借6楼数据-------------------------- SELECT Name FROM A WHERE ID = 2007 INTERSECT SELECT Name FROM A WHERE ID = 2008 Name b
from [thetable] a
where a.[ID]=2007
and exists (select 1
from [thetable] b
where b.[ID]=2008
and a.[Name]=b.[Name]
SELECT * FROM A WHERE ID = 2007
INTERSECT
SELECT * FROM A WHERE ID = 2008
(
SELECT [name] FROM [tb] WHERE [id] IN('a','b')
GROUP BY [name]
HAVING COUNT( DISTINCT [id]) >= 2
)b
ON a.[name]=b.[name]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[name] VARCHAR(10))
INSERT [tb]
SELECT 2006,'a' UNION ALL
SELECT 2006,'b' UNION ALL
SELECT 2006,'c' UNION ALL
SELECT 2007,'aa' UNION ALL
SELECT 2007,'b' UNION ALL
SELECT 2007,'c' UNION ALL
SELECT 2008,'a' UNION ALL
SELECT 2008,'b' UNION ALL
SELECT 2008,'cc'
--------------开始查询--------------------------SELECT a.* FROM [tb] a JOIN
(
SELECT [name] FROM [tb] WHERE [id] IN(2007,2008)
GROUP BY [name]
HAVING COUNT( DISTINCT [id]) >= 2
)b
ON a.[name]=b.[name]
----------------结果----------------------------
/*
id name
----------- ----------
2007 b
2006 b
2008 b(3 行受影响)
*/
(
SELECT [name] FROM [tb] WHERE [id] IN(2007,2008)
GROUP BY [name]
HAVING COUNT( DISTINCT [id]) >= 2
)b
ON a.[name]=b.[name] [color=#FF0000]AND a.[id] IN(2007,2008) [/color
少加一个条件
IF OBJECT_ID('[A]') IS NOT NULL DROP TABLE A
GO
CREATE TABLE A([id] INT,[name] VARCHAR(10))
INSERT A
SELECT 2006,'a' UNION ALL
SELECT 2006,'b' UNION ALL
SELECT 2006,'c' UNION ALL
SELECT 2007,'aa' UNION ALL
SELECT 2007,'b' UNION ALL
SELECT 2007,'c' UNION ALL
SELECT 2008,'a' UNION ALL
SELECT 2008,'b' UNION ALL
SELECT 2008,'cc'
--------------借6楼数据--------------------------
SELECT Name FROM A WHERE ID = 2007
INTERSECT
SELECT Name FROM A WHERE ID = 2008
Name
b