有这样一个数据表:
课程编号 收费编号 收费名称 收费金额 报名人数
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非常感谢!
课程编号 收费编号 收费名称 收费金额 报名人数
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非常感谢!
非常感谢
wufeng4552
的热心帮助。
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
gowith 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,记得结贴哦