求助！！！如何关联两张表后取出一个表中相同字段的数据

[Damon56]

条件
表1 XXX
字段：ORDER,
表2 YYY
字段：ORDER,PPPORDER，AGETYPE
ORDER, PPPORDER，AGETYPE
1234 8888 1
1243 8888 2
1324 8888 3
表1的ORDER=表2的ORDER
需求
关联表1和表2后，取出PPPORDER相同的数据，AGETYPE必须同时包含有（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
*/

[吉普赛的歌]

把测试数据发完整（两个表都有数据， 而且稍多一点， 是结果集中的不是结果集中的都有一点）， 保证可以查结果出来

[Damon56]

条件 表1 XXX 字段：ORDER,DATE ORDER, DATE 1234 2018 1243 2018 1324 2018 2345 2018 3564 2018 6756 2018 8759 2018 表2 YYY 字段：ORDER,PPPORDER，AGETYPE ORDER, PPPORDER，AGETYPE 1234 8888 1 1243 8888 2 1324 8888 3 2345 9999 1 3564 9999 2 6756 7777 1 8759 7777 3 表1的ORDER=表2的ORDER 需求 关联表1和表2后，取出PPPORDER相同的数据，AGETYPE必须同时包含有（1.2.3）值.取出像PPPORDER=8888 这类数据

[Hello World,]

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';