表a 包括有name,type 2个字段
name type
n72 1
w85 2
n72 3
w85 4
kkk 3
jjj 4
kkk 4
要查出有相同name而且typebu等于1,2的记录,
这个重复的值必须要有类型1,2的在内,就是3和4都有kkk,但1和2没有的话bu计算在内以上要查出name type
n72 3
w85 4
name type
n72 1
w85 2
n72 3
w85 4
kkk 3
jjj 4
kkk 4
要查出有相同name而且typebu等于1,2的记录,
这个重复的值必须要有类型1,2的在内,就是3和4都有kkk,但1和2没有的话bu计算在内以上要查出name type
n72 3
w85 4
SELECT *
FROM [A]
WHERE NAME IN(
select DISTINCT name
from A
WHERE TYPE=1 OR TYPE=2)
AND TTPE<>1 OR TTPE<>2
select name,type from a where name in(select name from a where type in (1,2)) and type not in(1,2)
WHERE NAME IN (SELECT NAME FROM TB WHERE TYPE IN(1,2))
AND TYPE NOT IN (1,2)
WHERE NAME IN (SELECT NAME FROM TB WHERE TYPE IN(1,2))
AND TYPE NOT IN (1,2)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-28 15:46:55
-- 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]([name] varchar(3),[type] int)
insert [tb]
select 'n72',1 union all
select 'w85',2 union all
select 'n72',3 union all
select 'w85',4 union all
select 'kkk',3 union all
select 'jjj',4 union all
select 'kkk',4
--------------开始查询--------------------------
select
*
from
tb
where
name in(select name from tb where type in (1,2)) and type not in(1,2)----------------结果----------------------------
/* name type
---- -----------
n72 3
w85 4(2 行受影响)
*/
GOCREATE TABLE dbo.T([name] NVARCHAR(10),[type] TINYINT);INSERT INTO dbo.T SELECT
'n72', 1
UNION all
SELECT 'n83' ,1
UNION all
SELECT 'w85' ,2
UNION all
SELECT 'w86' ,2
UNION all
SELECT 'n72' , 3
UNION all
SELECT'w85' , 4
UNION all
SELECT'kkk' , 3
UNION all
SELECT'jjj' , 4
UNION all
SELECT'kkk' , 4
UNION all
SELECT'n72' , 1
UNION all
SELECT'w85' , 2
SELECT * FROM dbo.T;WITH T AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY [name]) Rn FROM dbo.T
)
,T1 AS
(
SELECT * FROM T A WHERE Rn>1 AND EXISTS(SELECT 1 FROM T B WHERE A.[name]=B.[name] AND B.type IN (1,2))
)
SELECT * FROM T1
DROP TABLE dbo.T