declare @test table(机型 varchar(1), 工序编号 int, 时间 int) insert into @test select 'A', 12, 3 union all select 'A', 13, 5 union all select 'A', 17, 4 union all select 'B', 13, 5 union all select 'B', 14, 7 union all select 'B', 17, 5 union all select 'C', 12, 8 union all select 'C', 19, 9 union all select 'D', 17, 7 union all select 'D', 19, 8 union all select 'D', 20, 4select * from @test t where not exists(select 1 from @test where t.机型=机型 and t.工序编号=17 and 工序编号=12)/* 机型 工序编号 时间 ---- ----------- ----------- A 12 3 A 13 5 B 13 5 B 14 7 B 17 5 C 12 8 C 19 9 D 17 7 D 19 8 D 20 4*/
--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([机型] VARCHAR(1),[工序编号] INT,[时间] INT) INSERT [tb] SELECT 'A',12,3 UNION ALL SELECT 'A',13,5 UNION ALL SELECT 'A',17,4 UNION ALL SELECT 'B',13,5 UNION ALL SELECT 'B',14,7 UNION ALL SELECT 'B',17,5 UNION ALL SELECT 'C',12,8 UNION ALL SELECT 'C',19,9 UNION ALL SELECT 'D',17,7 UNION ALL SELECT 'D',19,8 UNION ALL SELECT 'D',20,4 --------------开始查询--------------------------SELECT * FROM [tb] AS t WHERE NOT EXISTS(SELECT 1 FROM tb AS a WHERE a.[工序编号]=12 AND t.[工序编号]=17 AND a.[机型]=t.[机型])
----------------结果---------------------------- /* 机型 工序编号 时间 A 12 3 A 13 5 B 13 5 B 14 7 B 17 5 C 12 8 C 19 9 D 17 7 D 19 8 D 20 4 */
SELECT * FROM [tb] AS t WHERE NOT EXISTS(SELECT 1 FROM tb AS a WHERE a.[工序编号]=12 AND t.[工序编号]=17 AND a.[机型]=t.[机型])
insert into @test
select 'A', 12, 3 union all
select 'A', 13, 5 union all
select 'A', 17, 4 union all
select 'B', 13, 5 union all
select 'B', 14, 7 union all
select 'B', 17, 5 union all
select 'C', 12, 8 union all
select 'C', 19, 9 union all
select 'D', 17, 7 union all
select 'D', 19, 8 union all
select 'D', 20, 4select * from @test t
where not exists(select 1 from @test where t.机型=机型 and t.工序编号=17 and 工序编号=12)/*
机型 工序编号 时间
---- ----------- -----------
A 12 3
A 13 5
B 13 5
B 14 7
B 17 5
C 12 8
C 19 9
D 17 7
D 19 8
D 20 4*/
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([机型] VARCHAR(1),[工序编号] INT,[时间] INT)
INSERT [tb]
SELECT 'A',12,3 UNION ALL
SELECT 'A',13,5 UNION ALL
SELECT 'A',17,4 UNION ALL
SELECT 'B',13,5 UNION ALL
SELECT 'B',14,7 UNION ALL
SELECT 'B',17,5 UNION ALL
SELECT 'C',12,8 UNION ALL
SELECT 'C',19,9 UNION ALL
SELECT 'D',17,7 UNION ALL
SELECT 'D',19,8 UNION ALL
SELECT 'D',20,4
--------------开始查询--------------------------SELECT * FROM [tb] AS t
WHERE NOT EXISTS(SELECT 1 FROM tb AS a WHERE a.[工序编号]=12 AND t.[工序编号]=17 AND a.[机型]=t.[机型])
----------------结果----------------------------
/*
机型 工序编号 时间
A 12 3
A 13 5
B 13 5
B 14 7
B 17 5
C 12 8
C 19 9
D 17 7
D 19 8
D 20 4
*/
SELECT * FROM [tb] AS t
WHERE NOT EXISTS(SELECT 1 FROM tb AS a WHERE a.[工序编号]=12 AND t.[工序编号]=17 AND a.[机型]=t.[机型])