SQL 分组查询
有表A
id answerid answerText category title order
1 nk01 das 3 kkk 0
2 nk02 5 aaa 0
3 nk03 6 bbb 0
3 nk03-1 6 ccc 0
4 nk04 7 eee 1
4 nk04-1 7 fff 3
4 nk04-2 7 ggg 2
需要得到结果集
id result
1 nk01:das
2 nk02:aaa
3 nk03:bbb、nk03-1:ccc
4 nk04:eee、nk04-2:ggg、nk04-2:fff
说明： 当category=3的时候 result字段的值为answerid字段值 + ':' + answerText字段值
当category=5的时候 result字段的值为answerid字段值 + ':'+ title字段值
当category=6的时候 根据id分组数据数来拼接字符串 result字段的值为answerid字段值 + ':' + title 字段值 + "、" + answerid字段值 + ':' + title字段值....根据该组数据数
当category=7的时候 id分组数拼接字符串，与=6类似，但是字符串拼接顺序需要根据order字段从小到大进行拼接。 求SQL语句
SQL
[解决办法]
create table tb(id int,answerid varchar(50),answertest varchar(50),category int,title char(10),[order] int )
insert tb select 1, 'nk01', 'das', 3 , 'kkk', 0
insert tb select 2, 'nk02', '' ,5 , 'aaa' , 0
insert tb select 3, 'nk03' , '' ,6 , 'bbb' , 0
insert tb select 3, 'nk03-1', '' , 6 , 'ccc' , 0
insert tb select 4, 'nk04' , '' ,7 , 'eee' , 1
insert tb select 4, 'nk04-1' , '' ,7 , 'fff' , 3
insert tb select 4, 'nk04-2' , '' ,7 , 'ggg' , 2
select category,result=stuff((select '、'+result from
( select case when category=3 then answerid+ ';' +answertest
when category=5 then answerid+';'+title
when category=6 then answerid+';'+title
when category=7 then answerid+';'+title else '' end as result,category from tb )a
where a.category=b.category for xml path('')),1,1,'') from tb b group by category
/*
category result
----------- ----------------------------------------------------------------------------------------------------------------
3 nk01;das
5 nk02;aaa
6 nk03;bbb 、nk03-1;ccc
7 nk04;eee 、nk04-1;fff 、nk04-2;ggg
(4 行受影响)
select category,result=stuff((select '、'+result from
( select case when category=3 then answerid+ ';' +answertest
when category=5 then answerid+';'+title
when category=6 then answerid+';'+title
when category=7 then answerid+';'+title else '' end as result,category,[order] from tb )a
where a.category=b.category order by a.[order] for xml path('')),1,1,'') from tb b group by category
/*
category result
----------- ----------------------------------------------------------------------------------------------------------------
3 nk01;das
5 nk02;aaa
6 nk03;bbb 、nk03-1;ccc
7 nk04;eee 、nk04-2;ggg 、nk04-1;fff
(4 行受影响)
select id,result=stuff((select '、'+result from
( select case when category=3 then answerid+ ';' +answertest
when category=5 then answerid+';'+title
when category=6 then answerid+';'+title
when category=7 then answerid+';'+title else '' end as result,category,[order],id from tb )a
where a.category=b.category order by a.[order] for xml path('')),1,1,'') from tb b group by category ,id
/*
id result
----------- ----------------------------------------------------------------------------------------------------------------
1 nk01;das
2 nk02;aaa
3 nk03;bbb 、nk03-1;ccc
4 nk04;eee 、nk04-2;ggg 、nk04-1;fff
(4 行受影响)
