高手们帮帮忙看看怎么写这个SQL
表结构如下:
DptID--部门号 Month--月份 Money --营业额
求一条SQL得出以下结果:
部门号 季度营业金额 季度
问题点数：50、回复次数：12Top
1 楼simonhehe（流氓会武术,谁也挡不住）回复于 2006-09-01 20:32:49 得分 0
----------看一下例子
--以下问题如果当字段ID不是按规则递增时，结果就不对了
------可以通过增加临时表,加一个规则递增的字段就ok了
----------------------------------------------------------------------------------------------------------------------------------
declare @t table(id int,name varchar(4),a int,b int,c int,d int,e int)
insert into @t select 1 ,'一',2,1,8,5,2
insert into @t select 2 ,'二',2,4,3,3,2
insert into @t select 3 ,'三',3,6,1,5,1
insert into @t select 4 ,'四',3,2,0,6,4
insert into @t select 5 ,'五',2,9,8,7,3
insert into @t select 6 ,'六',4,1,1,1,2
insert into @t select 7 ,'七',1,1,3,1,2
insert into @t select 8 ,'八',9,2,1,1,9
insert into @t select 9 ,'九',5,3,3,8,7
insert into @t select 10,'十',8,4,3,0,0
select
id,
name=(case when (id-1)/3 is null then '合计' when id is null then '小计' else name end),
a=sum(a),
b=sum(b),
c=sum(c),
d=sum(d),
e=sum(e)
from
@t
group by
(id-1)/3,id,name
with rollup
having grouping(name)=1 and grouping(id)=1Top
2 楼hellowork（一两清风）回复于 2006-09-01 20:43:11 得分 0
----如果月份是数值型
declare @t table(DptID varchar(10),[Month] int,[Money] money)
insert @t
select '1',6,100 union all
select '1',7,100 union all
select '1',8,100 union all
select '2',6,100 union all
select '2',7,100
SELECT DptID as 部门号,sum([Money]) as 季度营业额,
季度 =
case
when [Month] in(1,2,3) then 1
when [Month] in(4,5,6) then 2
when [Month] in(7,8,9) then 3
when [Month] in(10,11,12) then 4
end
FROM @t group by DptID,
case
when [Month] in(1,2,3) then 1
when [Month] in(4,5,6) then 2
when [Month] in(7,8,9) then 3
when [Month] in(10,11,12) then 4
end
ORDER BY 1
GO
----如果月份是字符型
declare @t table(DptID varchar(10),[Month] varchar(8),[Money] money)
insert @t
select '1','200606',100 union all
select '1','200607',100 union all
select '1','200608',100 union all
select '2','200606',100 union all
select '2','200607',100
SELECT DptID as 部门号,sum([Money]) as 季度营业额,
季度 = datepart(qq,cast([Month] + '01' as datetime))
FROM @t group by DptID,datepart(qq,cast([Month] + '01' as datetime))
ORDER BY 1Top
3 楼hnzyun（清）回复于 2006-09-01 20:51:08 得分 0
我那个把月份化成季度的应该怎么转换
我这样写 成吗 ？
select Dptid as '部门号',sum(money) as '季度营业金额',季度=
case when month/3 <=1 then 1 when month/3<=2 then 2 when month/3<=3 then 3 else 4 end
from table
group by Dptid,convert(int,month/3 )
请帮我看看
我这里没有机器Top
4 楼free_pop2k（每天进步一点点,日子就会好一点...）回复于 2006-09-01 20:55:22 得分 0
declare @T1 table (DptID int,[Month] int,[Money] decimal(8,2))
insert into @T1
select 1,1,100.02
union all
select 2,2,105.54
union all
select 2,3,500
union all
select 4,1,800
union all
select 1,5,7888
select * from @T1
declare @T2 table([季度] int)
insert into @T2
select 1
union all
select 2
union all
select 3
union all
select 4
--select * from (select distinct [DptID] from @T1) as a cross join @T2
select b.*,
[季度营业金额]=isnull((case b.[季度]
when 1
then (select sum(isnull([Money],0)) from @T1 where DptID=b.DptID and [month]<4 )
when 2
then (select sum(isnull([Money],0)) from @T1 where DptID=b.DptID and [month]<7 and [month]>3 )
when 3
then (select sum(isnull([Money],0)) from @T1 where DptID=b.DptID and [month]<10 and [month]>6 )
when 4
then (select sum(isnull([Money],0)) from @T1 where DptID=b.DptID and [month]>9 )
end),0.00)
from
(select * from (select distinct [DptID] from @T1) as a cross join @T2) as bTop
5 楼FreenHand（）回复于 2006-09-02 09:58:41 得分 0
用整除取余数的函数
结合INT函数和Group函数就可以了Top
6 楼kulongus（公司会计说：零钱太少了，你还是半年领一次工资吧）回复于 2006-09-13 10:06:30 得分 0
markTop
7 楼dawugui（潇洒老乌龟）回复于 2006-09-13 10:42:12 得分 0
select dptid , sum(case when [month] between 1 and 3 then money else 0 end ) as '季度营业金额' , 1 as '季度'
from table
group by dpt
union
select dpt , sum(case when [month] between 4 and 6 then money else 0 end ) as '季度营业金额' , 2 as '季度'
from table
group by dpt
union
select dptid , sum(case when [month] between 7 and 9 then money else 0 end ) as '季度营业金额' , 3 as '季度'
from table
group by dpt
union
select dpt , sum(case when [month] between 10 and 12 then money else 0 end ) as '季度营业金额' , 4 as '季度'
from table
group by dpt
Top
8 楼dawugui（潇洒老乌龟）回复于 2006-09-13 10:49:26 得分 0
select dptid , sum(case when [month] between 1 and 3 then money else 0 end ) as '季度营业金额' , 1 as '季度'
from table
group by dptid
union
select dptid , sum(case when [month] between 4 and 6 then money else 0 end ) as '季度营业金额' , 2 as '季度'
from table
group by dptid
union
select dptid , sum(case when [month] between 7 and 9 then money else 0 end ) as '季度营业金额' , 3 as '季度'
from table
group by dptid
union
select dptid , sum(case when [month] between 10 and 12 then money else 0 end ) as '季度营业金额' , 4 as '季度'
from table
group by dptid
Top
9 楼dawugui（潇洒老乌龟）回复于 2006-09-13 10:50:03 得分 0
我上面第一个DPT写错了,应该是DPTID,不好意思.Top
10 楼wisdomone（）回复于 2006-09-13 15:42:10 得分 0
upTop
11 楼yjlhch（爱拼才会赢）回复于 2006-09-13 15:44:11 得分 0
学习Top
12 楼hnzyun（清）回复于 2006-09-18 18:54:24 得分 0
謝謝大家的支持！Top
