请教一个Sql语句：
我的表MyGroups如下：
SN(编号) UserName(组员名称) Groups(所属组别)
109 张三 甲组
110 李四 甲组
110 李四 乙组
110 李四 丙组
331 王五 甲组
331 王五 丙组
332 赵六 丙组
332 赵六 丁组
332 赵六 戊组
333 刘七 甲组
333 刘七 乙组
333 刘七 戊组
..............
如何得到
109 张三 甲组
110 李四 甲组,乙组,丙组
331 王五 甲组,丙组
332 赵六 丙组,丁组,戊组
333 刘七 甲组,乙组,戊组
...................
[解决办法]
create table MyGroups(SN int, UserName varchar(10), Groups varchar(10))
insert MyGroups select 109, '张三 ', '甲组 '
union all select 110, '李四 ', '甲组 '
union all select 110, '李四 ', '乙组 '
union all select 110, '李四 ', '丙组 '
union all select 331, '王五 ', '甲组 '
union all select 331, '王五 ', '丙组 '
union all select 332, '赵六 ', '丙组 '
union all select 332, '赵六 ', '丁组 '
union all select 332, '赵六 ', '戊组 '
union all select 333, '刘七 ', '甲组 '
union all select 333, '刘七 ', '乙组 '
union all select 333, '刘七 ', '戊组 '
create function fun(@SN int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re= ' '
select @re=@re+ ', '+Groups from MyGroups where SN=@SN
return stuff(@re, 1, 1, ' ')
end
select SN, UserName, Groups=dbo.fun(SN)
from MyGroups
group by SN, UserName
--result
SN UserName Groups
----------- ---------- ----------------------------------------------------------------------------------------------------------------
109 张三 甲组
110 李四 甲组,乙组,丙组
331 王五 甲组,丙组
332 赵六 丙组,丁组,戊组
333 刘七 甲组,乙组,戊组
(5 row(s) affected)
[解决办法]
--臨時表
create table MyGroups(SN int, UserName varchar(10), Groups varchar(10))
insert MyGroups select 109, '张三 ', '甲组 '
union all select 110, '李四 ', '甲组 '
union all select 110, '李四 ', '乙组 '
union all select 110, '李四 ', '丙组 '
union all select 331, '王五 ', '甲组 '
union all select 331, '王五 ', '丙组 '
union all select 332, '赵六 ', '丙组 '
union all select 332, '赵六 ', '丁组 '
union all select 332, '赵六 ', '戊组 '
union all select 333, '刘七 ', '甲组 '
union all select 333, '刘七 ', '乙组 '
union all select 333, '刘七 ', '戊组 '
select SN, UserName, Groups=cast(Groups as varchar(200)) into #T from MyGroups order by SN
declare @SN int, @Groups varchar(200)
update #T set
@Groups=case when SN=@SN then @Groups+ ', '+Groups else Groups end,
@SN=SN,
Groups=@Groups
select SN, UserName,Groups=max(Groups) from #T
group by SN, UserName
order by SN
--result
SN UserName Groups
----------- ---------- --------------------------------------------------------------------------------------------
109 张三 甲组
110 李四 甲组,乙组,丙组
331 王五 甲组,丙组
332 赵六 丙组,丁组,戊组
333 刘七 甲组,乙组,戊组
(5 row(s) affected)
[解决办法]
select distinct x.sn,a.username,c.groups+ ', '+d.groups+ ', '+e.groups from MyGroups as a,
(
select SN from MyGroups group by SN
) x
left join
(
select top1 b.sn,b.groups from MyGroups as b where sn=110 order by groups asc FETCH FIRST 1 ROWS ONLY
)
as c on c.sn=x.sn
left join
(
select top 1 b.sn,b.groups from MyGroups as b where sn=110 order by groups desc FETCH FIRST 1 ROWS ONLY
)
as d on d.sn=x.sn
left join
(
select * from
(
select top 1 b.sn,b.groups as groups from MyGroups as b where sn=110 order by groups desc FETCH FIRST 2 ROWS ONLY
)xx order by xx.groups asc
)
as e on e.sn=x.sn
where x.sn=a.sn
and
x.sn=110
and
a.sn=110
