select 培训班名称,sum(1) as 人数,sum(学习天数) as 天数,sum(1)*sum(学习天数) as 人天数
from tbTemp group by 培训班名称
union all
select 培训班名称='总计',sum(人数) as 人数,null as 天数,sum(人天数) as 人天数
from (
select 培训班名称,sum(1) as 人数,sum(学习天数) as 天数,sum(1)*sum(学习天数) as 人天数
from tbTemp group by 培训班名称
) as x
from tbTemp group by 培训班名称
union all
select 培训班名称='总计',sum(人数) as 人数,null as 天数,sum(人天数) as 人天数
from (
select 培训班名称,sum(1) as 人数,sum(学习天数) as 天数,sum(1)*sum(学习天数) as 人天数
from tbTemp group by 培训班名称
) as x
select srt=1,* from(
select 培训班名称,sum(1) as 人数, sum(天数) as 天数,sum(1)*sum(天数) as 人天数
from tbtemp group by 培训班名称
) a
union all
select srt=2,'合计',* from (
select sum(1) as 人数, sum(天数) as 天数,sum(1)*sum(天数) as 人天数
from tbtemp) b
) a order by srt
from tbTemp
group by 培训班名称
union all --这个已经可以保证顺序
select '总计',sum(人数),null,sum(人天数)
from (
select 培训班名称,人数=count(*),天数=sum(学习天数),人天数=count(*)*sum(学习天数)
from tbTemp
group by 培训班名称
)a
select * from (
select srt=1,* from(
select 培训班名称,sum(1) as 人数, sum(学习天数) as 天数,sum(1)*sum(学习天数) as 人天数
from tbtemp group by 培训班名称
) a
union all
select srt=2,'合计',* from (
select sum(1) as 人数, sum(学习天数) as 天数,
(select sum(tot) from
(select sum(1)*sum(学习天数) as tot
from tbtemp group by 培训班名称 ) a ) as 人天数
from tbtemp ) b
) a order by srt
from tbTmp
group by 培训班名称
union all
select '合计',count(*),null ,count(*)*sum(学习天数)
from tbTmp
from tbTemp
group by 培训班名称
from tbTemp
group by 培训班名称
compute count(*)
Insert into @Test Select '工商管理','张三',10
Union All Select '工商管理','李四',12
Union All Select '工商管理','王五',6
Union All Select '财务管理','赵六',8
Union All Select '财务管理','冯七',7
Union All Select '财务管理','马八',11
Union All Select '英语作文','成长',2
Union All Select '英语作文','林枫',7Select 培训班名称,人数=Count(姓名),天数=Sum(学习天数),人天数=Count(姓名)*Sum(学习天数) From @Test
Group by 培训班名称
Union All
Select '合计',人数=Sum(人数),天数='',人天数=Sum(人天数) From (Select 培训班名称,人数=Count(姓名),天数=Sum(学习天数),人天数=Count(姓名)*Sum(学习天数) From @Test
Group by 培训班名称) S
非常感谢你的帮助,结果如下,已经很接近要求了,
培训班名称 人数 天数 人天数
财务管理 1 8 8
财务管理 2 18 36
工商管理 2 16 32
工商管理 1 12 12
英语作文 1 7 7
英语作文 1 2 2
总计 8 NULL 97但是我的要求是:
培训班名称 人数 天数 人天数
工商管理 3 28 84
财务管理 3 26 78
英语作文 2 9 18
合计 8 谢谢!
Insert into @Test Select '工商管理','张三',10
Union All Select '工商管理','李四',12
Union All Select '工商管理','王五',6
Union All Select '财务管理','赵六',8
Union All Select '财务管理','冯七',7
Union All Select '财务管理','马八',11
Union All Select '英语作文','成长',2
Union All Select '英语作文','林枫',7select 培训班名称,sum(1) as 人数,sum(学习天数) as 天数,sum(1)*sum(学习天数) as 人天数
from @Test group by 培训班名称
union all
select 培训班名称='总计',sum(人数) as 人数,null as 天数,sum(人天数) as 人天数
from (
select 培训班名称,sum(1) as 人数,sum(学习天数) as 天数,sum(1)*sum(学习天数) as 人天数
from @Test group by 培训班名称
) as x/*
结果:
培训班名称 人数 天数 人天数
-------------------- ----------- ----------- -----------
财务管理 3 26 78
工商管理 3 28 84
英语作文 2 9 18
总计 8 NULL 180(所影响的行数为 4 行)
*/
CREATE TABLE [dbo].[tbTemp] (
[培训班名称] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[姓名] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[天数] [int] NULL
) ON [PRIMARY]然后,再将记录添加进去的,用大家提供的语句确实得到了不同的记录集。
是不是我的表建的不对?
在下愚钝,还烦请各位再指教一下,谢谢。
而是你copy语句后修改得不对
你不修改直接运行
select 培训班名称,sum(1) as 人数,sum(学习天数) as 天数,sum(1)*sum(学习天数) as 人天数
from tbTemp group by 培训班名称
union all
select 培训班名称='总计',sum(人数) as 人数,null as 天数,sum(人天数) as 人天数
from (
select 培训班名称,sum(1) as 人数,sum(学习天数) as 天数,sum(1)*sum(学习天数) as 人天数
from tbTemp group by 培训班名称
) as x或者select 培训班名称,人数=count(*),天数=sum(学习天数),人天数=count(*)*sum(学习天数)
from tbTemp
group by 培训班名称
union all --这个已经可以保证顺序
select '总计',sum(人数),null,sum(人天数)
from (
select 培训班名称,人数=count(*),天数=sum(学习天数),人天数=count(*)*sum(学习天数)
from tbTemp
group by 培训班名称
)a