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

你胖到影响我了 .Net 2015-07-16 04:36:38

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

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

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

...全文

1106 点赞收藏 9

写回复

9 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

xiaoxiangqing 2015-07-17

参考3楼就行了

回复赞

挥泪斩女友 2015-07-16

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

回复赞

Pact_Alice 2015-07-16

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

回复赞

Pact_Alice 2015-07-16

3072398276

回复赞

你胖到影响我了 2015-07-16

引用 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 求解~~

回复赞

AcHerat 2015-07-16

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

回复赞

许晨旭 2015-07-16

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 行受影响)

回复赞

你胖到影响我了 2015-07-16

引用 1 楼 tcmakebest 的回复: