--------------------------------第1题----------------------------表名 销售表: NO , NO2, 数量 日期
1234 567890 33.5 2004-12-21
1234 598701 44.8 2004-11-21
1234 598701 45.2 2004-10-01
1234 567890 66.5 2004-9-21
3456 789065 22.5 2004-10-01
3456 789065 77.5 2004-10-27
3456 678901 48.5 2004-12-21 按月统计销售表中货物的销售量数
查询结果如下:
No, No2 , 九月, 十月,十一月,十二月
1234,567890, 66.5 , 0 , 0 , 33.5
1234,598701, 0 , 45.2, 44.8, 0
3456,789065, 0 , 100, 0 , 0
3456,678901, 0 , 0, 0 , 48.5
测试数据
create table sales
(
empid int,
proid int,
num float,
saDate datetime
)
go
insert into sales values(1234,567890,33.5,'2004-12-21')
insert into sales values(1234,598701,44.8,'2004-11-21')
insert into sales values(1234,598701,45.2,'2004-10-01')
insert into sales values(1234,567890,66.5,'2004-9-21')
insert into sales values(3456,789065,22.5,'2004-10-01')
insert into sales values(3456,789065,77.5,'2004-10-27')
insert into sales values(3456,678901,48.5,'2004-12-21')
--------------------------------第2题----------------------------
这样的汇总如何实现,表结构如下: A B C D
--------------------------
王小 10 正 二班
李大 20 正 一班
张五 15 负 一班
赵三 40 负 二班
王小 5 负 二班
.... ... ... ....
-------------------------------------
汇总时按字段D 和A分组,汇总字段B,C可以不显示,
如果C为'负'则对应的B为负数.结果如下:A B D
-------------------------
王小 5 二班
李大 20 一班
张五 -15 一班
赵三 -40 二班测试数据
create table ABC
(
A varchar(10),
B int,
C char(2),
D varchar(10)
)
insert into ABC values('王小',10,'正','二班')
insert into ABC values('李大',20,'正','一班')
insert into ABC values('张五',15,'负','一班')
insert into ABC values('赵三',40,'负','二班')
insert into ABC values('王小',5 ,'负','二班')
上面是两个题
我磨蹭了一晚上都没弄出来,到此求助高手
1234 567890 33.5 2004-12-21
1234 598701 44.8 2004-11-21
1234 598701 45.2 2004-10-01
1234 567890 66.5 2004-9-21
3456 789065 22.5 2004-10-01
3456 789065 77.5 2004-10-27
3456 678901 48.5 2004-12-21 按月统计销售表中货物的销售量数
查询结果如下:
No, No2 , 九月, 十月,十一月,十二月
1234,567890, 66.5 , 0 , 0 , 33.5
1234,598701, 0 , 45.2, 44.8, 0
3456,789065, 0 , 100, 0 , 0
3456,678901, 0 , 0, 0 , 48.5
测试数据
create table sales
(
empid int,
proid int,
num float,
saDate datetime
)
go
insert into sales values(1234,567890,33.5,'2004-12-21')
insert into sales values(1234,598701,44.8,'2004-11-21')
insert into sales values(1234,598701,45.2,'2004-10-01')
insert into sales values(1234,567890,66.5,'2004-9-21')
insert into sales values(3456,789065,22.5,'2004-10-01')
insert into sales values(3456,789065,77.5,'2004-10-27')
insert into sales values(3456,678901,48.5,'2004-12-21')
--------------------------------第2题----------------------------
这样的汇总如何实现,表结构如下: A B C D
--------------------------
王小 10 正 二班
李大 20 正 一班
张五 15 负 一班
赵三 40 负 二班
王小 5 负 二班
.... ... ... ....
-------------------------------------
汇总时按字段D 和A分组,汇总字段B,C可以不显示,
如果C为'负'则对应的B为负数.结果如下:A B D
-------------------------
王小 5 二班
李大 20 一班
张五 -15 一班
赵三 -40 二班测试数据
create table ABC
(
A varchar(10),
B int,
C char(2),
D varchar(10)
)
insert into ABC values('王小',10,'正','二班')
insert into ABC values('李大',20,'正','一班')
insert into ABC values('张五',15,'负','一班')
insert into ABC values('赵三',40,'负','二班')
insert into ABC values('王小',5 ,'负','二班')
上面是两个题
我磨蹭了一晚上都没弄出来,到此求助高手
2.
select t1.a,t1.d,sum(t1.b) as b
from
(select a,d,case when c='正' then B else -b end as B
from abc ) t1
group by a,d
order by a
select empid,proid,
'九月'=sum(case when datepart(mm,sadate)=9 then num
end) ,
'十月'=sum(case when datepart(mm,sadate)=10 then num end) ,
'十一月'=sum(case when datepart(mm,sadate)=11 then num end) ,
'十二月'=sum(case when datepart(mm,sadate)=12 then num end) from sales group by empid,proid
select a,d,b=sum(case when c='正' then b else -b end ) from abc group by a,d
(
empid int,
proid int,
num float,
saDate datetime
)
insert into sales values(1234,567890,33.5,'2004-12-21')
insert into sales values(1234,598701,44.8,'2004-11-21')
insert into sales values(1234,598701,45.2,'2004-10-01')
insert into sales values(1234,567890,66.5,'2004-9-21')
insert into sales values(3456,789065,22.5,'2004-10-01')
insert into sales values(3456,789065,77.5,'2004-10-27')
insert into sales values(3456,678901,48.5,'2004-12-21')
go
create view aa
as
select empid,proid,sum(num) as num,convert(char(7),sadate,120)as month from sales group by empid,proid,convert(char(7),sadate,120)
go
CREATE PROCEDURE bb
AS
declare @s varchar(8000)
set @s=''
select @s=@s+','''+(case substring(month,6,2) when '01' then '一月' when '02' then '二月' when '03' then '三月'
when '04' then '四月' when '05' then '五月' when '06' then '六月' when '07'
then '七月' when '08' then '八月'when '09' then '九月' when '10' then '十月'
when '11' then '十一月' when '12' then '十二月' end)+
'''=max(case month when '''+month+''' then num else ''0'' end )'
from aa group by monthexec('select empid,proid '+@s+',sum(num) as 年度合计 '+
'from aa group by empid,proid')
GO
drop table sales
drop view aa
drop procedure bb
--测试结果
1234 567890 66.5 0.0 0.0 33.5 100.0
1234 598701 0.0 45.200000000000003 44.799999999999997 0.0 90.0
3456 678901 0.0 0.0 0.0 48.5 48.5
3456 789065 0.0 100.0 0.0 0.0 100.0
create table ABC
(
A varchar(10),
B int,
C char(2),
D varchar(10)
)
insert into ABC values('王小',10,'正','二班')
insert into ABC values('李大',20,'正','一班')
insert into ABC values('张五',15,'负','一班')
insert into ABC values('赵三',40,'负','二班')
insert into ABC values('王小',5 ,'负','二班')select A,sum(case C when '负' then -B else B end)as B,D from ABC
group by A,D
--测试结果
王小 5 二班
赵三 -40 二班
李大 20 一班
张五 -15 一班
set @sql='select empid,proid'
select @sql=@sql+',sum(case when datepart(mm,sadate)= '''+ rtrim(mm)+''' then num else 0 end) ['+rtrim(mm)+'月]'
from (select distinct datepart(mm,sadate) mm from sales) a
select @sql=@sql+' from sales group by empid,proid' --print @sql
exec (@sql)
----------------------------------结果
1234 567890 66.5 0.0 0.0 33.5
1234 598701 0.0 45.2 44.8 0.0
3456 678901 0.0 0.0 0.0 48.5
3456 789065 0.0 100.0 0.0 0.0
sum(case when '正' then (+B) else (-B) end)
再group by A
就可以得到答案了
否则不是同一年的数据也会加在同一条记录上.
select empid,proid,
sum((case month(sadate) when 9 then num else 0 end))as 九月,
sum((case month(sadate) when 10 then num else 0 end))as 十月,
sum((case month(sadate) when 11 then num else 0 end))as 十一月,
sum((case month(sadate) when 12 then num else 0 end))as 十二月
from sales
group by empid,proid2:
select a,
sum(case c when '正' then b else (-1)*b end)as 总,d
from abc
group by a,d
order by a,d
on a9.empid=sales.empid and a9.proid=sales.proidleft join(select distinct empid,proid,sum(num) as num10 from sales where saDate>='2004-10-01'and saDate<='2004-10-31' group by empid,proid) as a10
on a10.empid=sales.empid and a10.proid=sales.proidleft join(select distinct empid,proid,sum(num) as num11 from sales where saDate>='2004-11-01'and saDate<='2004-10-30' group by empid,proid) as a11
on a11.empid=sales.empid and a11.proid=sales.proidleft join(select distinct empid,proid,sum(num) as num12 from sales where saDate>='2004-12-01'and saDate<='2004-12-31' group by empid,proid) as a12
on a12.empid=sales.empid and a12.proid=sales.proid
1234 567890 66.5 0.0 0.0 33.5
1234 598701 0.0 45.200000000000003 0.0 0.0
3456 678901 0.0 0.0 0.0 48.5
3456 789065 0.0 100.0 0.0 0.0