sql语句一对多 join出一条数据

[你胖到影响我了]

功能是这样 我有两张表 一张表数据多 一张表数据少
table1 table2
T1 T2
a a
b a
a
b
b

我想查出来table1的数据只有一条 但对应的table2的数据要全部显示

比如说 t1的a对应t2的aaa 这样的一条语句

[tcmakebest]

楼主举的例子仍然太抽象了

[你胖到影响我了]

引用 1 楼 tcmakebest 的回复:

楼主举的例子仍然太抽象了

- - 客户要求就是这么抽象

[许晨旭]

CREATE TABLE #TABLE1
(
T1 VARCHAR(20)
)
INSERT INTO #TABLE1 VALUES('A'),('B')

CREATE TABLE #TABLE2
(
T2 VARCHAR(20)
)
INSERT INTO #TABLE2 VALUES('A'),('A'),('A'),('A'),('B'),('B'),('B'),('B')

SELECT 
	T1,
	(SELECT ''+T2
	 FROM #TABLE2
	 WHERE #TABLE2.T2=#TABLE1.T1
	 FOR XML PATH('')
	)
FROM #TABLE1

T1                   
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A                    AAAA
B                    BBBB

(2 行受影响)

[AcHerat]

select * from table1 a left join table2 b on a.a = b.a

[你胖到影响我了]

引用 3 楼 Landa_Ran 的回复:

CREATE TABLE #TABLE1
(
T1 VARCHAR(20)
)
INSERT INTO #TABLE1 VALUES('A'),('B')

CREATE TABLE #TABLE2
(
T2 VARCHAR(20)
)
INSERT INTO #TABLE2 VALUES('A'),('A'),('A'),('A'),('B'),('B'),('B'),('B')

SELECT 
	T1,
	(SELECT ''+T2
	 FROM #TABLE2
	 WHERE #TABLE2.T2=#TABLE1.T1
	 FOR XML PATH('')
	)
FROM #TABLE1

T1                   
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A                    AAAA
B                    BBBB

(2 行受影响)

po主 是这样的 T1 的列是主键 T2 的列 值是varchar T _ T 求解~~

[Pact_Alice]

3072398276

[Pact_Alice]

CREATE TABLE #t1 (NAME VARCHAR(20)) INSERT INTO #t1 SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c' CREATE TABLE #t2 (NAME VARCHAR(20)) INSERT INTO #t2 SELECT 'a' UNION ALL SELECT 'a' UNION ALL SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'b' UNION ALL SELECT 'c' UNION ALL SELECT 'c' UNION ALL SELECT 'c' ;WITH cet AS ( SELECT t1.NAME, t2.NAME AS Names, dense_rank()OVER( ORDER BY t2.NAME)ID FROM #t2 t2 LEFT JOIN #t1 t1 ON t1.NAME=t2.NAME ) SELECT (STUFF((SELECT ' '+Names FROM cet c WHERE c.ID=c1.ID FOR XML PATH('')),1,1,'') )AS NAME FROM cet c1 GROUP BY id

[挥泪斩女友]

这个容易，表一group by having count等于一，插到临时表，再jion表二

[xiaoxiangqing]

参考3楼就行了