34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT 1234 AS [ORDER],2018 AS [DATE]
INTO #a
UNION ALL SELECT 1243,2018
UNION ALL SELECT 1324,2018
UNION ALL SELECT 2345,2018
UNION ALL SELECT 3564,2018
UNION ALL SELECT 6756,2018
UNION ALL SELECT 8759,2018
SELECT 1234 AS [ORDER] ,8888 AS PPPORDER,1 AS AGETYPE
INTO #b
UNION ALL SELECT 1243,8888,2
UNION ALL SELECT 1324,8888,3
UNION ALL SELECT 2345,9999,1
UNION ALL SELECT 3564,9999,2
UNION ALL SELECT 6756,7777,1
UNION ALL SELECT 8759,7777,3
--排序处理
SELECT DISTINCT PPPORDER,AGETYPE INTO #c FROM #b ORDER BY PPPORDER,AGETYPE
SELECT #b.*
FROM #a
INNER JOIN #b ON #b.[ORDER] = #a.[ORDER]
INNER JOIN ( SELECT DISTINCT PPPORDER ,
STUFF(
( SELECT DISTINCT ','
+ CONVERT(VARCHAR(20), AGETYPE)
FROM #c b
WHERE b.PPPORDER = a.PPPORDER
FOR XML PATH('')) ,
1 ,
1 ,
'') AS AGETYPEs
FROM #c a ) c ON c.PPPORDER = #b.PPPORDER
AND c.AGETYPEs = '1,2,3';
USE tempdb
GO
IF OBJECT_ID('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID('t2') IS NOT NULL DROP TABLE t2
GO
CREATE TABLE t1([ORDER] INT,[DATE] INT)
CREATE TABLE t2([ORDER] INT,[PPPORDER] INT,[AGETYPE] INT)
GO
INSERT INTO t1
SELECT 1234,2018
UNION ALL SELECT 1243,2018
UNION ALL SELECT 1324,2018
UNION ALL SELECT 2345,2018
UNION ALL SELECT 3564,2018
UNION ALL SELECT 6756,2018
UNION ALL SELECT 8759,2018
INSERT INTO t2
SELECT 1234,8888, 1
UNION ALL SELECT 1243,8888, 2
UNION ALL SELECT 1324,8888, 3
UNION ALL SELECT 2345,9999, 1
UNION ALL SELECT 3564,9999, 2
UNION ALL SELECT 6756,7777, 1
UNION ALL SELECT 8759,7777, 3
;WITH cte AS (
SELECT b.* FROM t1 AS a INNER JOIN t2 AS b ON a.[order]=b.[order] AND b.PPPORDER=8888
WHERE EXISTS(
SELECT 1 FROM t2 AS b2 WHERE b2.AGETYPE IN(1,2,3) AND a.[order]=b2.[order]
)
)
SELECT * FROM cte AS a WHERE EXISTS
(
SELECT 1 FROM cte AS b WHERE b.AGETYPE=1
)AND EXISTS(
SELECT 1 FROM cte AS b WHERE b.AGETYPE=2
)AND EXISTS(
SELECT 1 FROM cte AS b WHERE b.AGETYPE=3
)
/*
ORDER PPPORDER AGETYPE
1234 8888 1
1243 8888 2
1324 8888 3
*/