--测试数据 if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([oderID] int,[color] nvarchar(22)) Insert #T select 1,N'红色' union all select 1,N'黄色' union all select 2,N'红色' union all select 2,N'绿色' union all select 3,N'黄色' union all select 3,N'绿色' union all select 4,N'红色' union all select 4,N'黄色' Go --测试数据结束 SELECT a.oderID FROM #T a JOIN #T b ON b.oderID = a.oderID WHERE a.color = '红色' AND b.color = '黄色';
Create table #T([oderID] int,[color] nvarchar(22)) Insert #T select 1,N'红色' union all select 1,N'黄色' union all select 2,N'红色' union all select 2,N'绿色' union all select 3,N'黄色' union all select 3,N'绿色' union all select 4,N'红色' union all select 4,N'黄色'select * from #Tselect oderID from #T where color=N'红色'and oderID in (select oderID from #T where color=N'黄色')
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([oderID] int,[color] nvarchar(22))
Insert #T
select 1,N'红色' union all
select 1,N'黄色' union all
select 2,N'红色' union all
select 2,N'绿色' union all
select 3,N'黄色' union all
select 3,N'绿色' union all
select 4,N'红色' union all
select 4,N'黄色'
Go
--测试数据结束
SELECT a.oderID
FROM #T a
JOIN #T b
ON b.oderID = a.oderID
WHERE a.color = '红色'
AND b.color = '黄色';
Insert #T
select 1,N'红色' union all
select 1,N'黄色' union all
select 2,N'红色' union all
select 2,N'绿色' union all
select 3,N'黄色' union all
select 3,N'绿色' union all
select 4,N'红色' union all
select 4,N'黄色'select * from #Tselect oderID from #T where color=N'红色'and oderID in (select oderID from #T where color=N'黄色')