请教高手一个查询语句，感觉没有头绪

[leagiboy]

有这样一个数据表：
课程编号 收费编号 收费名称 收费金额 报名人数
102345 1 学费 100 12
102345 2 材料费 50 12
102345 4 杂费 10 12
102346 2 材料费 50 20
102346 4 杂费 10 20
102349 1 学费 100 10
102349 2 材料费 50 10
102349 4 杂费 10 10
101343 1 学费 100 11
101343 3 材料费 40 11
101343 4 杂费 10 11
101346 1 学费 100 16
101346 3 材料费 40 16
101346 4 杂费 10 16
如何得到如下的查询结果：
类别 课程编码 收费编号 收费名称 收费金额 收费小计 人数合计
101 101343,101346 1 学费 100 150 27
101 101343,101346 3 材料费 40 150 27
101 101343,101346 4 杂费 10 150 27
102 102345,102349 1 学费 100 160 22
102 102345,102349 2 材料费 50 160 22
102 102345,102349 4 杂费 10 160 22
102 102346 2 材料费 50 60 20
102 102346 4 杂费 10 60 20

非常感谢！

[水族杰纶]

人数为什么是27
不是33

[水族杰纶]

也说说后面这么得到
规则是什么

[leagiboy]

查询要求是这样的，按照课程编码的前三位、收费标准进行分类，所以分类是101的课程，有两个101343,101346，而且收费标准一致，所以人数合计是101343的人数11+101346的人数16是27。

[leagiboy]

不好意思，帖子没有编辑权限，没有讲清楚，抱歉。
1、按照课程编码前三位、课程收费标准即收费编码和收费名称作为分类依据；
2、统计项目有：收费标准相同课程编码用逗号分隔汇总，如101343,101346；
3、收费标准小计；
4、当前课程人数合计；

[水族杰纶]

还是没看懂
102346 也是102分类的
为什么不和 102345,102349 放在一起
不应该是 102345,102349,102346吗

[leagiboy]

以课程编码前三位和收费项目作为分类条件：
102346收费项目只有两个，而另外的102345,102349 收费项目是3个，收费项目也是分类条件，所以分开放。

[水族杰纶]

[Quote=引用 6 楼 leagiboy 的回复:]
以课程编码前三位和收费项目作为分类条件：
102346收费项目只有两个，而另外的102345,102349 收费项目是3个，收费项目也是分类条件，所以分开放。
[/Quote]
102 102346 2 材料费 50 60 20
102 102346 4 杂费 10 60 20
同样是
102 同样是材料费 为什么不和上面相同 102 材料费合并

竟然是必须三个才合并

[leagiboy]

课程102346的收费项目数是2个，而另外102345,102349项目数是3个，也是就是说分类相同情况下，按照课程的收费标准分类，从课程角度讲，102346的收费标准是60（材料费+杂费），102345,102349收费标准是160（学费+材料费+杂费），所以不能单独就具体某个收费项目合并。
非常感谢
wufeng4552
的热心帮助。

[飘零一叶]

分类标准太乱了

[dawugui]

你们都说乱,我就不看了.帮顶.

[coleling]

create table tb(课程编号 varchar(50),收费编号 int,收费名称 varchar(20),收费金额 int,报名人数 int)
insert tb select '102345', 1, '学费', 100, 12
insert tb select '102345', 2, '材料费', 50, 12
insert tb select '102345', 4, '杂费', 10, 12
insert tb select '102346', 2, '材料费', 50, 20
insert tb select '102346', 4, '杂费', 10, 20
insert tb select '102349', 1, '学费', 100, 10
insert tb select '102349', 2, '材料费', 50, 10
insert tb select '102349', 4, '杂费', 10, 10
insert tb select '101343', 1, '学费', 100, 11
insert tb select '101343', 3, '材料费', 40, 11
insert tb select '101343', 4, '杂费', 10, 11
insert tb select '101346', 1, '学费', 100, 16
insert tb select '101346', 3, '材料费', 40, 16
insert tb select '101346', 4, '杂费', 10, 16
go

with t1 as
(
	select 课程编号,收费小计 = sum(收费金额) from tb group by 课程编号
)
,t2 as
(
	select 类别 = left(课程编号,3),课程编号,课程编码 = stuff((select ','+课程编号 from t1 where left(课程编号,3) = left(a.课程编号,3) and 收费小计 = a.收费小计 for xml path('')),1,1,''),收费小计 
	from t1 a group by 课程编号,收费小计
)
select a.类别,a.课程编码,b.收费编号,b.收费名称,b.收费金额,a.收费小计,人数合计=sum(b.报名人数) 
from t2 a join tb b on a.课程编号 = b.课程编号 
group by a.类别,a.课程编码,a.收费小计,b.收费编号,b.收费名称,b.收费金额

/*
类别     课程编码        收费编号  收费名称 收费金额 收费小计 人数合计
------- -------------- -------- ------- ------- ------- --------
101	101343,101346	1	学费	100	150	27
101	101343,101346	3	材料费	40	150	27
101	101343,101346	4	杂费	10	150	27
102	102345,102349	1	学费	100	160	22
102	102345,102349	2	材料费	50	160	22
102	102345,102349	4	杂费	10	160	22
102	102346	2	材料费	50	60	20
102	102346	4	杂费	10	60	20
*/

如OK，记得结贴哦

[leagiboy]

感谢大家，尤其coleling，结贴了。