一个联接查询有关问题

一个联接查询问题
表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 
 
[解决办法]
探讨


修正下
SQL code
if object_id(N'[A]') is not null drop table [A]
go
if object_id(N'[B]') is not null drop table [B]
go
create table [A]([uid] int,[uname] varchar(10))
go
create table [B]([sid] int,[uid……