求报表统计SQL

[symbol441]

表workers(workid,workName)
表sales(saleid,workid,saledate)

统计业务员指定年度每季度的销售量信息

最后呈现为这种形式

workName 年度 Total 第一季度 第二季度 第三季度 第四季度
----------------------------- ----------- ----------- ----------- ----------- ----------- -----------
张三 1993 50 0 50 0 0
李四 1993 55 25 30 0 0
XX 1993 85 0 85 0 0
五五 1993 60 35 0 0 25
Total 1993 250 60 165 0 25

在此先谢过各位朋友了

[ming_Y]

请给出表结构!

[ming_Y]

表数据

[SQL77]

行转列的帖子,

[playwarcraft]

表sales 应该还有个数字栏位。。。

[andysun88]

表sales 应该还有个数字栏位。。,估计是，
应为销售量无法计算，难道一条就是一个吗？

[nianran520]

select t.workName,
year(r.saledate) as 年度,
count(r.saleid) as Total,
sum(case datepart(quarter,r.saledate) when 1 then 1 else 0 end) as 第一季度,
sum(case datepart(quarter,r.saledate) when 2 then 1 else 0 end) as 第二季度,
sum(case datepart(quarter,r.saledate) when 3 then 1 else 0 end) as 第三季度,
sum(case datepart(quarter,r.saledate) when 4 then 1 else 0 end) as 第四季度
from workers t join sales r
on t.workid = r.workid
where year(r.saledate) = 1993
group by t.workName,year(r.saledate)
union all
select 'Total',1993,count(1),
sum(case datepart(quarter,r.saledate) when 1 then 1 else 0 end) as 第一季度,
sum(case datepart(quarter,r.saledate) when 2 then 1 else 0 end) as 第二季度,
sum(case datepart(quarter,r.saledate) when 3 then 1 else 0 end) as 第三季度,
sum(case datepart(quarter,r.saledate) when 4 then 1 else 0 end) as 第四季度
from sales

[--小F--]

select
  a.workName,year(b.saledate) as 年度,sum(b.saleid) as Total,
  max(case month(b.saledate) between 1 and 3 then saleid else '' end) as '第一季度',
  max(case month(b.saledate) between 4 and 6 then saleid else '' end) as '第二季度',
  max(case month(b.saledate) between 7 and 9 then saleid else '' end) as '第三季度',  
  max(case month(b.saledate) between 10 and 12 then saleid else '' end) as '第四季度'
from
  workers a,sales b
where
  a.workid=b.workid
group by
 a.workName,year(b.saledate)
with rollup

[--小F--]

select
  a.workName,year(b.saledate) as 年度,count(b.saleid) as Total,
  max(case month(b.saledate) between 1 and 3 then saleid else '' end) as '第一季度',
  max(case month(b.saledate) between 4 and 6 then saleid else '' end) as '第二季度',
  max(case month(b.saledate) between 7 and 9 then saleid else '' end) as '第三季度',  
  max(case month(b.saledate) between 10 and 12 then saleid else '' end) as '第四季度'
from
  workers a,sales b
where
  a.workid=b.workid
group by
 a.workName,year(b.saledate)
with rollup

[黄_瓜]

SQL codeselect
  isnull(a.workName,Total),year(b.saledate) as 年度,sum(b.saleid) as Total,
  max(case month(b.saledate) between 1 and 3 then saleid else '' end) as '第一季度',
  max(case month(b.saledate) between 4 and 6 then saleid else '' end) as '第二季度',
  max(case month(b.saledate) between 7 and 9 then saleid else '' end) as '第三季度',  
  max(case month(b.saledate) between 10 and 12 then saleid else '' end) as '第四季度'
from  workers a,sales b   where  a.workid=b.workid
group by a.workName,year(b.saledate)
with rollup

[symbol441]

[Quote=引用 4 楼 playwarcraft 的回复:]
表sales 应该还有个数字栏位。。。
[/Quote]
表sales(saleid,workid,saledate，amount)
谢谢朋友们的提醒。确实是，每一笔交易有一笔金额,amout即为每笔交易所产生的交易额。

[nianran520]

--要哪个年份自己改
select t.workName,
year(r.saledate) as 年度,
count(r.saleid) as Total,
sum(case datepart(quarter,r.saledate) when 1 then amount else 0 end) as 第一季度,
sum(case datepart(quarter,r.saledate) when 2 then amount else 0 end) as 第二季度,
sum(case datepart(quarter,r.saledate) when 3 then amount else 0 end) as 第三季度,
sum(case datepart(quarter,r.saledate) when 4 then amount else 0 end) as 第四季度
from workers t join sales r
on t.workid = r.workid
where year(r.saledate) = 1993
group by t.workName,year(r.saledate)
union all
select 'Total',1993 as 年度,
count(1) as Total,
sum(case datepart(quarter,r.saledate) when 1 then amount else 0 end) as 第一季度,
sum(case datepart(quarter,r.saledate) when 2 then amount else 0 end) as 第二季度,
sum(case datepart(quarter,r.saledate) when 3 then amount else 0 end) as 第三季度,
sum(case datepart(quarter,r.saledate) when 4 then amount else 0 end) as 第四季度
from sales
where year(saledate) = 1993

[nianran520]

--要哪个年份自己改
select t.workName,
year(r.saledate) as 年度,
sum(r.amount) as Total,
sum(case datepart(quarter,r.saledate) when 1 then r.amount else 0 end) as 第一季度,
sum(case datepart(quarter,r.saledate) when 2 then r.amount else 0 end) as 第二季度,
sum(case datepart(quarter,r.saledate) when 3 then r.amount else 0 end) as 第三季度,
sum(case datepart(quarter,r.saledate) when 4 then r.amount else 0 end) as 第四季度
from workers t join sales r
on t.workid = r.workid
where year(r.saledate) = 1993
group by t.workName,year(r.saledate)
union all
select 'Total',1993 as 年度,
sum(amount) as Total,
sum(case datepart(quarter,r.saledate) when 1 then amount else 0 end) as 第一季度,
sum(case datepart(quarter,r.saledate) when 2 then amount else 0 end) as 第二季度,
sum(case datepart(quarter,r.saledate) when 3 then amount else 0 end) as 第三季度,
sum(case datepart(quarter,r.saledate) when 4 then amount else 0 end) as 第四季度
from sales
where year(saledate) = 1993

[yananguo_1985]

select
  a.workName,year(b.saledate) as 年度,sum(b.amount) as Total,
  sum(case month(b.saledate) between 1 and 3 then amount else 0 end) as '第一季度',
  sum(case month(b.saledate) between 4 and 6 then amount else 0 end) as '第二季度',
  sum(case month(b.saledate) between 7 and 9 then amount else 0 end) as '第三季度',  
  sum(case month(b.saledate) between 10 and 12 then amount else 0 end) as '第四季度'
from  workers a,sales b   where  a.workid=b.workid
group by a.workName,year(b.saledate)
with rollup

[symbol441]

谢谢各位，问题解决
特别感谢nianran520兄
结贴了