两表联合查询问题

[chenchenecho]

表1
BOMID PartNo Qty
BOM001 L00001 1
BOM001 L00002 1
BOM001 L00003 1
BOM002 L00001 1
BOM002 L00003 1
表2
Order PartNo Qty
M000001 L00001 1
M000001 L00002 1
M000001 L00003 1
M000002 L00001 1
M000002 L00003 1
结果
Order BOMID
M000001 BOM001
M000002 BOM002
意思是Order的Partno&qty要和BOM的partno&qty完全匹配上才可以，比如M00001是三条匹配，不是一行一行匹配
这个要怎么查才能查出来，谢谢各位大佬。

[骑着蜗牛去爬山]

with tb1(BOMID,PartNo,Qty)
as(select 'BOM001','L00001',1 union all
select 'BOM001','L00002',1 union all
select 'BOM001','L00003',1 union all
select 'BOM002','L00001',1 union all
select 'BOM002','L00003',1),
tb2([Order],PartNo,Qty) as(
select 'M000001','L00001',1 union all
select 'M000001','L00002',1 union all
select 'M000001','L00003',1 union all
select 'M000002','L00001',1 union all
select 'M000002','L00003',1
),
tb3 as(
select BOMID,vls=stuff((select ','+PartNo+'_'+rtrim(Qty) from tb1 t2 where t1.BOMID=t2.BOMID for xml path('')),1,1,'') from tb1 t1 group by BOMID
),
tb4 as(
select [Order],vls=stuff((select ','+PartNo+'_'+rtrim(Qty) from tb2 t2 where t1.[Order]=t2.[Order] for xml path('')),1,1,'') from tb2 t1 group by [Order]
)
select t1.BOMID,t2.[Order] from tb3 t1,tb4 t2 where t1.vls=t2.vls

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([BOMID] nvarchar(26),[PartNo] nvarchar(26),[Qty] int)
Insert #T1
select N'BOM001',N'L00001',1 union all
select N'BOM001',N'L00002',1 union all
select N'BOM001',N'L00003',1 union all
select N'BOM002',N'L00001',1 union all
select N'BOM002',N'L00003',1
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([Order] nvarchar(27),[PartNo] nvarchar(26),[Qty] int)
Insert #T2
select N'M000001',N'L00001',1 union all
select N'M000001',N'L00002',1 union all
select N'M000001',N'L00003',1 union all
select N'M000002',N'L00001',1 union all
select N'M000002',N'L00003',1
Go
--测试数据结束
;
WITH    cte
          AS ( SELECT   [Order] + '/' + BOMID AS col ,
                        COUNT(0) AS cnt
               FROM     #T2 a
                        JOIN #T1 b ON b.PartNo = a.PartNo
                                      AND b.Qty = a.Qty
               GROUP BY [Order] + '/' + BOMID
             )
    SELECT  SUBSTRING(col, 0, CHARINDEX('/', col)) AS [Order] ,
            SUBSTRING(col, CHARINDEX('/', col) + 1,
                      LEN(col) - CHARINDEX('/', col)) AS [BOMID]
    FROM    cte
    WHERE   ( SELECT    COUNT(0)
              FROM      #T2
              WHERE     col LIKE [Order] + '/%'
            ) = cnt
            AND ( SELECT    COUNT(0)
                  FROM      #T1
                  WHERE     col LIKE '%/' + [BOMID]
                ) = cnt