34,576
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
if not object_id(N'T') is null
drop table t
Go
Create table t([pName] nvarchar(10),[test] nvarchar(10))
Insert t
select N'产品1',N'A' union all
select N'产品1',N'B' union all
select N'产品1',N'C' union all
select N'产品1',N'D' union all
select N'产品1',N'E' union all
select N'产品2',N'A' union all
select N'产品2',N'C' union all
select N'产品2',N'E' union all
select N'产品3',N'B' union all
select N'产品3',N'C' union all
select N'产品3',N'D'
GO
SELECT tt.* FROM
(SELECT * FROM (SELECT pName FROM t GROUP BY pName) AS tt
CROSS JOIN (VALUES('A'),('B'),('C'),('D'),('E')) AS L(v)
) AS tt LEFT JOIN t ON tt.pName=t.pName AND tt.v=t.test
WHERE t.pName IS NULL
ORDER BY t.pName
/*
pName v
---------- ----
产品2 B
产品2 D
产品3 A
产品3 E
*/
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([name] nvarchar(23),[test] nvarchar(21))
Insert #T
select N'产品1',N'A' union all
select N'产品1',N'B' union all
select N'产品1',N'C' union all
select N'产品1',N'D' union all
select N'产品1',N'E' union all
select N'产品2',N'A' union all
select N'产品2',N'C' union all
select N'产品2',N'E' union all
select N'产品3',N'B' union all
select N'产品3',N'C' union all
select N'产品3',N'D'
GO
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([testname] nvarchar(21))
Insert #T1
select N'A' union all
select N'B' union all
select N'C' union all
select N'D' union all
select N'E'
Go
--测试数据结束
SELECT *
FROM
(SELECT DISTINCT name FROM #T) T1
CROSS APPLY
(SELECT * FROM #T1) T2
WHERE NOT EXISTS
(
SELECT * FROM #T WHERE T1.name = name AND T2.testname = test
);