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语句

[学习中-------]

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 行受影响)

[学习中-------]

引用 1 楼 myselfff 的回复:

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 行受影响)

[学习中-------]

引用 2 楼 myselfff 的回复:

[quote=引用 1 楼 myselfff 的回复:]

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 行受影响)

[/quote]

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 行受影响)