如果在 MS SQL Server 中,可以这样:select 科室,
sum(一季度费用) as 一季度费用,
sum(二季度费用) as 二季度费用,
sum(三季度费用) as 三季度费用,
sum(四季度费用) as 四季度费用
from (
select 科室,
sum(金额) as 一季度费用,
0 as 二季度费用,
0 as 三季度费用,
0 as 四季度费用
where 季度=1
group by 科室
union
select 科室,
0 as 一季度费用,
sum(金额) as 二季度费用,
0 as 三季度费用,
0 as 四季度费用
where 季度=2
group by 科室
union
select 科室,
0 as 一季度费用,
0 as 二季度费用,
sum(金额) as 三季度费用,
0 as 四季度费用
where 季度=3
group by 科室
union
select 科室,
0 as 一季度费用,
0 as 二季度费用,
0 as 三季度费用,
sum(金额) as 四季度费用
where 季度=4
group by 科室
) TmpTable group by 科室
不知 access 是否支持这样的语法。但你可以参考这个语句写程序:先对中间的4个 select 语句分别执行,结果存入一个表中,再从这个表求最后的合计。
sum(一季度费用) as 一季度费用,
sum(二季度费用) as 二季度费用,
sum(三季度费用) as 三季度费用,
sum(四季度费用) as 四季度费用
from (
select 科室,
sum(金额) as 一季度费用,
0 as 二季度费用,
0 as 三季度费用,
0 as 四季度费用
where 季度=1
group by 科室
union
select 科室,
0 as 一季度费用,
sum(金额) as 二季度费用,
0 as 三季度费用,
0 as 四季度费用
where 季度=2
group by 科室
union
select 科室,
0 as 一季度费用,
0 as 二季度费用,
sum(金额) as 三季度费用,
0 as 四季度费用
where 季度=3
group by 科室
union
select 科室,
0 as 一季度费用,
0 as 二季度费用,
0 as 三季度费用,
sum(金额) as 四季度费用
where 季度=4
group by 科室
) TmpTable group by 科室
不知 access 是否支持这样的语法。但你可以参考这个语句写程序:先对中间的4个 select 语句分别执行,结果存入一个表中,再从这个表求最后的合计。
2。Insert Into TmpTable (科室,一季度费用,二季度费用,三季度费用,四季度费用)
select 科室,
sum(金额) as 一季度费用,
0 as 二季度费用,
0 as 三季度费用,
0 as 四季度费用
from money
where 季度=1
group by 科室
union
select 科室,
0 as 一季度费用,
sum(金额) as 二季度费用,
0 as 三季度费用,
0 as 四季度费用
from money
where 季度=2
group by 科室
union
select 科室,
0 as 一季度费用,
0 as 二季度费用,
sum(金额) as 三季度费用,
0 as 四季度费用
from money
where 季度=3
group by 科室
union
select 科室,
0 as 一季度费用,
0 as 二季度费用,
0 as 三季度费用,
sum(金额) as 四季度费用
from money
where 季度=4
group by 科室3。select 科室,
sum(一季度费用) as 一季度费用,
sum(二季度费用) as 二季度费用,
sum(三季度费用) as 三季度费用,
sum(四季度费用) as 四季度费用
from TmpTable
group by 科室
得到结果。
SELECT 产品.产品名称, 订单.客户ID, Year([订购日期]) AS 订购年份
FROM 订单 INNER JOIN (产品 INNER JOIN 订单明细 ON 产品.产品ID = 订单明细.产品ID) ON 订单.订单ID = 订单明细.订单ID
WHERE (((订单.订购日期) Between #1/1/1997# And #12/31/1997#))
GROUP BY 产品.产品名称, 订单.客户ID, Year([订购日期])
PIVOT "第" & DatePart("q",[订购日期],1,0) & "季度" In ("第1季度","第2季度","第3季度","第4季度");
将科室作为行标题,季度作为列标题,Sum(费用)作为值,不就行了么?
我也建议用交叉查询,速度又快,又不会错.
1:如果时间的表示形式是“yyyy/mm/dd"的形式。你又不想加一个字段表示哪个季度
select a.科室,
q1=(select sum(金额) from money where 科室=a.科室 and month(时间)>=1 and month(时间)<=3),
q2=(select sum(金额) from money where 科室=a.科室 and month(时间)>=4 and month(时间)<=6),
q3=(select sum(金额) from money where 科室=a.科室 and month(时间)>=7 and month(时间)<=9),
q4=(select sum(金额) from money where 科室=a.科室 and month(时间)>=10 and month(时间)<=12)
from money a
group by a。科室
2.如果时间的表示形式里面有季度。或者你又想加一个字段表示哪个季度
那样的话你可以用一个单位距阵和这个表相乘
/*create table privot (q,01,02,03,04)*/
/*其录为
1,1,0,0,0
2,0,1,0,0
3,0,0,1,0
4,0,0,0,1
*/
select a.科室,q1=sum(a.金额*b.01),q2=sum(a.金额*b.02),q3=sum(a.金额*b.03),q4=sum(a.金额*b.04)
from money a,privot b
where a.季度=b.季度
group by a.科室