表A：
uid,uname
1 aaa
2 bbb
表B：
sid,uid, sname
1 1 eee
1 2 qqq
2 1 ggg
2 2 hhh
查询结果：
uid, uname, sid, sname
1 aaa 1,2 eee,qqq
2 bbb 1,2 ggg,hhh
求查询方法
------解决方案--------------------
用cross join
------解决方案--------------------
列值合并
------解决方案--------------------
你看下这个例子，对照改下
http://topic.csdn.net/u/20120805/15/77aba9bb-1a17-43e5-b449-e31c0e76fbe3.html
------解决方案--------------------
- SQL code
Declare @A Table(UID Int, UName Varchar(20))Declare @B Table(SID Int, UID Int, SName Varchar(20))Insert @A Select 1, 'AAA' Union All Select 2, 'BBB'Insert @B Select 1, 1, 'EEE' Union All Select 1, 2, 'QQQ' Union All Select 2, 1, 'GGG' Union All Select 2, 2, 'HHH'SELECT A.UID, A.UName, SIDS=CAST(MIN(B.SID) as varchar) + CASE WHEN COUNT(*)=1 THEN '' ELSE ','+CAST(MAX(B.SID) as varchar) END, SNames=CAST(MIN(B.SName) as varchar) + CASE WHEN COUNT(*)=1 THEN '' ELSE ','+CAST(MAX(B.SName) as varchar) ENDFROM @A As A, @B As B Where A.UID=B.UIDGROUP BY A.UID, A.UName/*(2 行受影响)(4 行受影响)UID UName SIDS SNames----------- -------------------- ------------------------- -------------------------1 AAA 1,2 EEE,GGG2 BBB 1,2 HHH,QQQ(2 行受影响)*/
------解决方案--------------------
- SQL code
Declare @A Table(UID Int, UName Varchar(20))Declare @B Table(SID Int, UID Int, SName Varchar(20))Insert @A Select 1, 'AAA' Union All Select 2, 'BBB'Insert @B Select 1, 1, 'EEE' Union All Select 1, 2, 'QQQ' Union All Select 2, 1, 'GGG' Union All Select 2, 2, 'HHH'SELECT A.UID, A.UName, SIDS=CAST(MIN(B.SID) as varchar) + CASE WHEN COUNT(*)=1 THEN '' ELSE ','+CAST(MAX(B.SID) as varchar) END, SNames=CAST(MIN(B.SName) as varchar) + CASE WHEN COUNT(*)=1 THEN '' ELSE ','+CAST(MAX(B.SName) as varchar) ENDFROM @A As A, @B As B Where A.UID=B.UIDGROUP BY A.UID, A.UName--这个方法比较简单
------解决方案--------------------
- SQL code
declare @表A table(uid int,uname varchar(5))insert into @表Aselect 1, 'aaa' union allselect 2, 'bbb'declare @表B table(sid int,uid int, sname varchar(5))insert into @表Bselect 1, 1, 'eee' union allselect 1, 2, 'qqq' union allselect 2, 1, 'ggg' union allselect 2, 2, 'hhh'select uid,uname,stuff((select ','+rtrim(sid) from @表B b where b.uid=a.uid for xml path('')),1,1,'') 'sid',stuff((select ','+sname from @表B b where b.uid=a.uid for xml path('')),1,1,'') 'sname'from @表A a/*uid uname sid sname----------- ----- ------------- -----------1 aaa 1,2 eee,ggg2 bbb 1,2 qqq,hhh(2 row(s) affected)*/
------解决方案--------------------
MSSQL2005及以上版本：
- SQL code
CREATE TABLE t1( id INT, name VARCHAR(10))INSERT INTO t1SELECT 1, 'aaa' UNION ALLSELECT 2, 'bbb'CREATE TABLE t2( id INT, tid INT, name VARCHAR(10))INSERT INTO t2SELECT 1, 1, 'eee' UNION ALLSELECT 1, 2, 'qqq' UNION ALLSELECT 2, 1, 'ggg' UNION ALLSELECT 2, 2, 'hhh'SELECT * FROM t1SELECT * FROM t2;WITH aaa AS( SELECT id,STUFF((SELECT ','+LTRIM(tid) FROM t2 AS b WHERE b.id=a.id FOR XML PATH('')),1,1,'') AS tid, STUFF((SELECT ','+NAME FROM t2 AS c WHERE c.id=a.id FOR XML PATH('')),1,1,'') AS name FROM t2 AS a GROUP BY id)SELECT a1.id AS [uid],a1.name AS uname,b1.tid AS [sid],b1.name AS sname FROM t1 AS a1 INNER JOIN aaa AS b1 ON a1.id=b1.id--------------------------uid uname sid sname1 aaa 1,2 eee,qqq2 bbb 1,2 ggg,hhh