日期:2014-05-17  浏览次数:20426 次

一个查询的问题,急!!!
C# code

CREATE TABLE #t1
(
AFromID INT,
AToUserId INT,
AUserName VARCHAR(20),
BFromID INT,
BToUserId INT, 
BUserName VARCHAR(20),
RelationID VARCHAR(1000),
RelationName VARCHAR(1000)
)

INSERT INTO #t1(
AFromID,
AToUserId,
AUserName,
BFromID,
BToUserId, 
BUserName
)
SELECT 16,6,'黄永友',6,207,'华附物理深化' UNION
SELECT 16,6,'黄永友',6,7,'卢福东' UNION
SELECT 16,5,'叶正波',5,8,'赵建辉' UNION
SELECT 16,6,'黄永友',6,8,'赵建辉'
select * FROM #t1
drop table #t1

/*
现在的结果
16    5    叶正波    5    8    赵建辉    NULL    NULL
16    6    黄永友    6    7    卢福东    NULL    NULL
16    6    黄永友    6    8    赵建辉    NULL    NULL
16    6    黄永友    6    207    华附    NULL    NULL

想要的结果
16    5    叶正波    5    8    赵建辉    5,6    叶正波,黄永友
16    6    黄永友    6    7    卢福东    6    黄永友
16    6    黄永友    6    207    华附    6    黄永友

*/






------解决方案--------------------
合并行集?
------解决方案--------------------
SQL code
CREATE TABLE #t1
(
AFromID INT,
AToUserId INT,
AUserName VARCHAR(20),
BFromID INT,
BToUserId INT, 
BUserName VARCHAR(20),
RelationID VARCHAR(1000),
RelationName VARCHAR(1000)
)

INSERT INTO #t1(
AFromID,
AToUserId,
AUserName,
BFromID,
BToUserId, 
BUserName
)
SELECT 16,6,'黄永友',6,207,'华附物理深化' UNION
SELECT 16,6,'黄永友',6,7,'卢福东' UNION
SELECT 16,5,'叶正波',5,8,'赵建辉' UNION
SELECT 16,6,'黄永友',6,8,'赵建辉'
select * FROM #t1;
--drop table #t1
with TT as(
select *,ROW_NUMBER() over(partition by BToUserId order by atouserid) as rowid
from #t1)

select x.*,y.RelationID,y.RelationName
from 
(
select AFromID,
AToUserId,
AUserName,
BFromID,
BToUserId, 
BUserName
from TT 
where rowid in (select min(rowid) from TT group by BToUserId)) as x
inner join (
select BToUserId,BUserName,stuff((select ','+CAST(atouserid as varchar(20)) from #t1  as b where b.BToUserId=a.BToUserId for xml path('')),1,1,'') as RelationID
,stuff((select ','+CAST(AUserName as varchar(20)) from #t1  as c where c.BToUserId=a.BToUserId for xml path('')),1,1,'') as RelationName
from #t1 as a
group by BToUserId,BUserName) as y on x.BToUserId=y.BToUserId