27,582
社区成员




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
--要哪个年份自己改
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
--要哪个年份自己改
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
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
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
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
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