求报表统计SQL

symbol441 2010-01-20 09:34:34
表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

在此先谢过各位朋友了
...全文
185 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
symbol441 2010-01-20
  • 打赏
  • 举报
回复
谢谢各位,问题解决
特别感谢nianran520兄
结贴了
yananguo_1985 2010-01-20
  • 打赏
  • 举报
回复

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
nianran520 2010-01-20
  • 打赏
  • 举报
回复
--要哪个年份自己改
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
nianran520 2010-01-20
  • 打赏
  • 举报
回复
--要哪个年份自己改
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
symbol441 2010-01-20
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 playwarcraft 的回复:]
表sales 应该还有个数字栏位。。。
[/Quote]
表sales(saleid,workid,saledate,amount)
谢谢朋友们的提醒。确实是,每一笔交易有一笔金额,amout即为每笔交易所产生的交易额。

黄_瓜 2010-01-20
  • 打赏
  • 举报
回复
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
--小F-- 2010-01-20
  • 打赏
  • 举报
回复
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
--小F-- 2010-01-20
  • 打赏
  • 举报
回复
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
nianran520 2010-01-20
  • 打赏
  • 举报
回复
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

andysun88 2010-01-20
  • 打赏
  • 举报
回复
表sales 应该还有个数字栏位。。,估计是,
应为销售量无法计算,难道一条就是一个吗?
playwarcraft 2010-01-20
  • 打赏
  • 举报
回复
表sales 应该还有个数字栏位。。。
SQL77 2010-01-20
  • 打赏
  • 举报
回复
行转列的帖子,
ming_Y 2010-01-20
  • 打赏
  • 举报
回复
表数据
ming_Y 2010-01-20
  • 打赏
  • 举报
回复
请给出表结构!

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧