200分求SQL查询统计,不难,大家来拿分,结贴时补齐分

ljupin 2008-11-27 03:58:42
现有5个表,最前面的一列都是自动编号)
一.分类表
分类ID, 分类名称
1 一类
2 二类
.............
二.物资表
物资ID, 物资名称, 物资单价, 分类ID
1 固化剂 20 2
2 塑料桶 10 1
3 加热板 35 2
............
三.部门表
部门ID, 部门名称
1 A部门
2 B部门
........
四.领用表
领用ID, 领用日期, 物资ID, 领用数量 领用部门ID
1 2008-10-20 2 20 1
2 2008-11-2 1 30 3
3 2008-11-25 2 40 2
........
五.计划表
计划ID, 部门ID, 月计划金额, 计划月份
1 1 2000 2008-10-1
2 2 1000 2008-10-1
3 3 3000 2008-10-1
.......
7 1 2100 2008-11-1
8 2 1100 2008-11-1
9 3 3100 2008-11-1
......

计划表中,每月会输入当月计划金额,以便和实际领用金额比较,计算出差值,做成本控制.请SQL高手帮忙写一下,非常感谢

要求结果1: 某年某月领用额和差额
'------------------------------------------------------------
xxxx年x月物资领用量
A部门 B部门 ....
一类 x￥ x￥
二类 x￥ x￥
.... ... ...
合计金额 x￥ x￥
计划金额 x￥ x￥
差额 x￥ x￥


'要求结果2: 指定日期内的领用量
'-------------------------------------------------
xxxx年x月x日---xxxx年x月x日物资领用量
A部门 B部门 ....
一类 x￥ x￥
二类 x￥ x￥
.... ... ...
合计金额 x￥ x￥
...全文
453 32 打赏 收藏 转发到动态 举报
写回复
用AI写文章
32 条回复
切换为时间正序
请发表友善的回复…
发表回复
chendi0301 2009-01-10
  • 打赏
  • 举报
 回复
真多高手,来学习
lintf1986 2008-12-03
  • 打赏
  • 举报
 回复 
create table 分类表(分类ID int , 分类名称 varchar(10)) 

insert into 分类表 values(1 , '一类') 

insert into 分类表 values(2 , '二类') 

insert into 分类表 values(3 , '三类')

create table 物资表(物资ID int, 物资名称 varchar(10), 物资单价 int, 分类ID int) 

insert into 物资表 values(1 , '固化剂' , 20 , 2 ) 

insert into 物资表 values(2 , '塑料桶' , 10 , 1 ) 

insert into 物资表 values(3 , '加热板' , 35 , 3 ) 

create table 部门表(部门ID int, 部门名称 varchar(10)) 

insert into 部门表 values(1 , 'A部门') 

insert into 部门表 values(2 , 'B部门')

insert into 部门表 values(3 , 'C部门') 

create table 领用表(领用ID int, 领用日期 datetime, 物资ID int, 领用数量 int, 领用部门ID int) 

insert into 领用表 values(1 , '2008-10-20', 2 , 20 , 1) 

insert into 领用表 values(2 , '2008-11-2' , 1 , 30 , 3) 

insert into 领用表 values(3 , '2008-11-25', 3 , 40 , 2) 

insert into 领用表 values(4 , '2008-11-26', 2 , 50 , 3) 

insert into 领用表 values(5 , '2008-11-27', 1 , 60 , 1)

insert into 领用表 values(6 , '2008-11-27', 3 , 60 , 1) 

create table 计划表(计划ID int, 部门ID int, 月计划金额 int, 计划月份 datetime) 

insert into 计划表 values(1 , 1 , 2000 , '2008-10-1') 

insert into 计划表 values(2 , 2 , 1000 , '2008-10-1') 

insert into 计划表 values(3 , 3 , 3000 , '2008-10-1') 

insert into 计划表 values(7 , 1 , 2100 , '2008-11-1') 

insert into 计划表 values(8 , 2 , 1100 , '2008-11-1') 

insert into 计划表 values(9 , 3 , 3100 , '2008-11-1') 



declare @sql varchar(4000)

set @sql='if (object_id(''temptd1'')) is not null

drop table temptd1 '

set @sql=@sql+'

if object_id(''temptd2'') is not null

drop table temptd2 '

set @sql=@sql+'

if object_id(''temptd3'') is not null

drop table temptd3 '

set @sql=@sql+'

if object_id(''temptd4'') is not null

drop table temptd4 '

set @sql=@sql+'

select * into temptd1 from'

set @sql=@sql+'

(select b.分类名称'

select @sql=@sql+',

max(case 部门名称 when '''+部门名称+''' then 消耗金额 else 0 end)'+'['+部门名称+']'

from (select distinct 部门名称 from 部门表) a

set @sql=@sql+' 

from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额

from 领用表,物资表,分类表,部门表

where 物资表.物资ID=领用表.物资ID 

and 分类表.分类ID=物资表.分类ID 

and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称) 表1 '



set @sql=@sql+'

select * into temptd2 from(select ''合计金额'' as 分类名称'

select @sql=@sql+',

sum('+部门名称+') ['+部门名称+']'

from(select distinct 部门名称 from 部门表) a

set @sql=@sql+' from temptd1) 表2 '



set @sql=@sql+'

select * into temptd3 from(select ''计划金额'' as 分类名称'

select @sql=@sql+',

sum(case 部门名称 when '''+部门名称+''' then 月计划金额 else 0 end) ['+部门名称+']'

from(select distinct 部门名称 from 部门表) a

set @sql=@sql+' 

from 部门表,计划表 where 计划表.部门ID=部门表.部门ID) 表3 '



set @sql=@sql+'

select * into temptd4 from(select ''差额'' as 分类名称'

select @sql=@sql+',

(temptd3.'+部门名称+'-temptd2.'+部门名称+') ['+部门名称+']'

from(select distinct 部门名称 from 部门表) a

set @sql=@sql+' from temptd2,temptd3) 表4'

set @sql=@sql+'



select * from temptd1

union all

(select * from temptd2)

union all

(select * from temptd3)

union all

(select * from temptd4)'

print @sql

exec(@sql)



--打印出来的SQL静态语句

if (object_id('temptd1')) is not null

drop table temptd1 

if object_id('temptd2') is not null

drop table temptd2 

if object_id('temptd3') is not null

drop table temptd3 

if object_id('temptd4') is not null

drop table temptd4 

select * into temptd1 from

(select b.分类名称,

max(case 部门名称 when 'A部门' then 消耗金额 else 0 end)[A部门],

max(case 部门名称 when 'B部门' then 消耗金额 else 0 end)[B部门],

max(case 部门名称 when 'C部门' then 消耗金额 else 0 end)[C部门] 

from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额

from 领用表,物资表,分类表,部门表

where 物资表.物资ID=领用表.物资ID 

and 分类表.分类ID=物资表.分类ID 

and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称) 表1 

select * into temptd2 from(select '合计金额' as 分类名称,

sum(A部门) [A部门],

sum(B部门) [B部门],

sum(C部门) [C部门] from temptd1) 表2 

select * into temptd3 from(select '计划金额' as 分类名称,

sum(case 部门名称 when 'A部门' then 月计划金额 else 0 end) [A部门],

sum(case 部门名称 when 'B部门' then 月计划金额 else 0 end) [B部门],

sum(case 部门名称 when 'C部门' then 月计划金额 else 0 end) [C部门] 

from 部门表,计划表 where 计划表.部门ID=部门表.部门ID) 表3 

select * into temptd4 from(select '差额' as 分类名称,

(temptd3.A部门-temptd2.A部门) [A部门],

(temptd3.B部门-temptd2.B部门) [B部门],

(temptd3.C部门-temptd2.C部门) [C部门] from temptd2,temptd3) 表4



select * from temptd1

union all

(select * from temptd2)

union all

(select * from temptd3)

union all

(select * from temptd4)



（所影响的行数为 3 行）





（所影响的行数为 1 行）





（所影响的行数为 1 行）





（所影响的行数为 1 行）





--结果如下：

--分类名称 A部门 B部门 C部门

--二类     1200  0    600

--三类     2100  1400 0

--一类     200   0    500

--合计金额 3500  1400 1100

--计划金额 4100  2100 6100

--差额     600   700  5000


楼主插入数据就可以直接用了
lintf1986 2008-12-03
  • 打赏
  • 举报
 回复 
create table 分类表(分类ID int , 分类名称 varchar(10)) 

insert into 分类表 values(1 , '一类') 

insert into 分类表 values(2 , '二类') 

insert into 分类表 values(3 , '三类')

create table 物资表(物资ID int, 物资名称 varchar(10), 物资单价 int, 分类ID int) 

insert into 物资表 values(1 , '固化剂' , 20 , 2 ) 

insert into 物资表 values(2 , '塑料桶' , 10 , 1 ) 

insert into 物资表 values(3 , '加热板' , 35 , 3 ) 

create table 部门表(部门ID int, 部门名称 varchar(10)) 

insert into 部门表 values(1 , 'A部门') 

insert into 部门表 values(2 , 'B部门')

insert into 部门表 values(3 , 'C部门') 

create table 领用表(领用ID int, 领用日期 datetime, 物资ID int, 领用数量 int, 领用部门ID int) 

insert into 领用表 values(1 , '2008-10-20', 2 , 20 , 1) 

insert into 领用表 values(2 , '2008-11-2' , 1 , 30 , 3) 

insert into 领用表 values(3 , '2008-11-25', 3 , 40 , 2) 

insert into 领用表 values(4 , '2008-11-26', 2 , 50 , 3) 

insert into 领用表 values(5 , '2008-11-27', 1 , 60 , 1)

insert into 领用表 values(6 , '2008-11-27', 3 , 60 , 1) 

create table 计划表(计划ID int, 部门ID int, 月计划金额 int, 计划月份 datetime) 

insert into 计划表 values(1 , 1 , 2000 , '2008-10-1') 

insert into 计划表 values(2 , 2 , 1000 , '2008-10-1') 

insert into 计划表 values(3 , 3 , 3000 , '2008-10-1') 

insert into 计划表 values(7 , 1 , 2100 , '2008-11-1') 

insert into 计划表 values(8 , 2 , 1100 , '2008-11-1') 

insert into 计划表 values(9 , 3 , 3100 , '2008-11-1') 



declare @sql varchar(4000)

set @sql='select b.分类名称'

select @sql=@sql+',max(case 部门名称 when '''+部门名称+''' then 消耗金额 else 0 end)'+'['+部门名称+']'

from (select distinct 部门名称 from 部门表) a

set @sql=@sql+' from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额

from 领用表,物资表,分类表,部门表

where 物资表.物资ID=领用表.物资ID 

and 分类表.分类ID=物资表.分类ID 

and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称'

print @sql

exec(@sql)



--打印出来的SQL静态语句

select b.分类名称,

max(case 部门名称 when 'A部门' then 消耗金额 else 0 end)[A部门],

max(case 部门名称 when 'B部门' then 消耗金额 else 0 end)[B部门],

max(case 部门名称 when 'C部门' then 消耗金额 else 0 end)[C部门] 

from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额

from 领用表,物资表,分类表,部门表

where 物资表.物资ID=领用表.物资ID 

and 分类表.分类ID=物资表.分类ID 

and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称



--结果如下：

--分类名称 A部门 B部门 C部门

--二类     1200  0    600

--三类     2100  1400 0

--一类     200   0    500
rover12421 2008-12-03
  • 打赏
  • 举报
 回复
不懂
看看
chaorenwopashei 2008-12-03
  • 打赏
  • 举报
 回复
我建的表怎么不能田间住外间关系啊，
让你望见影子的墙 2008-12-02
  • 打赏
  • 举报
 回复
果然好麻烦啊。
不过觉得效率应该不高。
-->>先把数据筛选出来
select 部门名称,分类名称,金额=物资单价*领用数量 ,月计划金额,差额 =月计划金额-物资单价*领用数量 into #t1
from 分类表,物资表,部门表,领用表,计划表
where 分类表.分类id=物资表.分类id and 物资表.物资id=领用表.物资id and 部门表.部门ID=领用表.领用部门id and 领用日期 between '2008-10-1' and '2008-10-31' and 部门表.部门ID=计划表.部门ID and 计划月份='2008-10-01 00:00:00.000'

-->>再进行行列转置
declare @f1 varchar(8000),@f2 varchar(8000),@f3 varchar(8000),@f4 varchar(8000),@sql varchar(8000)
select @f1='',@f2='',@f3='',@f4=''
select @f1=@f1+',['+部门名称+']='''+[部门名称]+''''
,@f2=@f2+','''+cast ([金额] as varchar(10))+''''
,@f3=@f3+','''+cast([月计划金额] as varchar(10))+''''
,@f4=@f4+','''+cast([差额] as varchar(10))+''''
from #t1
set @sql='select 分类=''一类'''+@f1
+' union all select ''金额'''+@f2
+' union all select ''月计划金额'''+@f3
+' union all select ''差额'''+@f4
exec(@sql)
print @sql

go
-->>统计
select id=identity( int ,1,1),* into #t2 from
(
select 分类名称=CASE WHEN GROUPING(分类名称)=0 THEN 分类名称
WHEN GROUPING(分类名称)=1 THEN '合计'
ELSE '' END
,部门A=sum(case 部门名称 when 'A部门' then 金额 end),部门B=sum(case 部门名称 when 'B部门' then 金额 end)

from #t1
group by 分类名称
with rollup
union all
select '计划金额',A部门=sum(case 部门名称 when 'A部门' then 月计划金额 end) ,
B部门=sum(case 部门名称 when 'B部门' then 月计划金额 end)
from
(
select 部门名称,月计划金额 from 部门表,计划表 where 部门表.部门ID=计划表.部门ID and 计划月份='2008-10-01 00:00:00.000') k
)L
-->>加入最后一行
select * from #t2
union all
select distinct'','差额',[部门A]=(select 部门A from #t2 where 分类名称='计划金额')-(select 部门A from #t2 where 分类名称='合计')
,[部门B]=(select 部门B from #t2 where 分类名称='计划金额')-(select 部门B from #t2 where 分类名称='合计')
from #t2


guyanxifei 2008-12-02
  • 打赏
  • 举报
 回复
不过我知道,如果你要做成本控制的话,前面输入计划金额,要求后面显示差额,
那么这几张表之间肯定要建立连接的关系,并且表之间要建立约束,这样才能防止出现冗余!
guyanxifei 2008-12-02
  • 打赏
  • 举报
 回复
不过我知道,如果你要做成本控制的话,前面输入计划金额,要求后面显示差额,
那么这几张表之间肯定要建立连接的关系,并且表之间要建立约束,这样才能防止出现冗余!
guyanxifei 2008-12-02
  • 打赏
  • 举报
 回复
对不起,我现在还正在学数据库,暂时不会写啊!
feifeiyiwen 2008-12-02
  • 打赏
  • 举报
 回复
我也有你一样的问题，领导让学多维数据库分析，创建维度可能解决你的问题。
ljupin 2008-11-28
  • 打赏
  • 举报
 回复
已经给出最简单的表结构了,居然没人能写一个?

汗,实际的表很复杂,分类是无限级的


昨天晚上我已经按分类,部门查询出来,但现在有最后合计的问题,算了,还是我自己弄吧!
ytx98 2008-11-27
  • 打赏
  • 举报
 回复
整一个存储过程吧,其实不难.
ljupin 2008-11-27
  • 打赏
  • 举报
 回复
顶起来,没人会吗????
ljupin 2008-11-27
  • 打赏
  • 举报
 回复
[Quote=引用 18 楼 dawugui 的回复:]
引用 15 楼 ljupin 的回复:
物资表中有单价,领用表中有领用数量,单价*数量就是领用金额
统计结果就是从领用表中计算 某月或某段时间 的领用金额(单价*数量)

这5个表要一起关联查询


看了半天没看懂你的关系,自己参考楼上贴的行列转换自己写吧.友情帮顶了.
[/Quote]

汗,这样都看不懂,那把金额看着数量行吗?
计划表中的计划金额改为计划数量

最后查询结果改为各分类的数量,合计数量,和计划数量比较

这样说懂了吗?
dawugui 2008-11-27
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 ljupin 的回复:]
物资表中有单价,领用表中有领用数量,单价*数量就是领用金额
统计结果就是从领用表中计算 某月或某段时间 的领用金额(单价*数量)

这5个表要一起关联查询
[/Quote]

看了半天没看懂你的关系,自己参考楼上贴的行列转换自己写吧.友情帮顶了.
WrriorKitten 2008-11-27
  • 打赏
  • 举报
 回复
学习
ljupin 2008-11-27
  • 打赏
  • 举报
 回复
先要从[领用表]中 按部门,类别,指定月份或日期查询出领用数量
然后乘以[物资表]中的单价,就是金额
所有类别的金额合计后的总金额和[计划表]中的月金额比价,就是差额
ljupin 2008-11-27
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 dawugui 的回复:]
你那些金额要通过什么关系获取呢?郁闷哦,以下是测试数据,供下面的人使用.

create table 分类表(分类ID int , 分类名称 varchar(10))
insert into 分类表 values(1 , '一类')
insert into 分类表 values(2 , '二类')
create table 物资表(物资ID int, 物资名称 varchar(10), 物资单价 int, 分类ID int)
insert into 物资表 values(1 , '固化剂' , 20 , 2 )
insert into 物资表 values(2 , '塑料桶' , 10 , 1 )
in…
[/Quote]

物资表中有单价,领用表中有领用数量,单价*数量就是领用金额
统计结果就是从领用表中计算 某月或某段时间 的领用金额(单价*数量)

这5个表要一起关联查询
wangxiao2008 2008-11-27
  • 打赏
  • 举报
 回复
幫頂~~~
shiyong281 2008-11-27
  • 打赏
  • 举报
 回复
还可以啊，有学过，不过忘的差不多了，顶！！！
加载更多回复（12）
MySQL中的一条统计SQL_mySql关于统计数量的SQL查询操作
我就废话不多说了，大家还是直接看代码吧~select project_no,sum(case when device_state=0 then 1 else 0 end)as offTotal ,sum(case when device_state=1 then 1 else 0 end)as onlineTotal,sum(1)totalfrom iot_d...
SQL数据补全问题
三、需求 （查询出每个学生每一们课程的分数，如果该学生在数据库中没有那门课的分数，则那门课分数置为0） 1.使用 cross join 对两张表做笛卡尔积。 SELECT * FROM sc CROSS JOIN class +--------+----------+----...
Oracle-Sql规范
1 SQL规范 1.1 SQL操作符优化 1.1.1 IN操作符 用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较... ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换
实习生和我的故事之统计日期补全法
今天整体来说很轻松，无...先把数据库的数据统计出来放入list里这个应该不难吧，不要说有些人不会写sql 算了贴出来吧 这样就统计出来这段时间的数据库里记录的数据了  那么问题就来了， 他问我，数据库中有些...
玩转MySQL：一站式解决分库分表后患问题方案
上篇分库分表文章中已经将分库分表的方法论全面阐述清楚了，总体看下来用一个字形容，那就是爽！尤其是分库分表技术能够让数据存储层真正成为三高架构，但前面爽是爽了，接着一起来看看分库分表后产生一系列的后患...
应用实例

27,580

社区成员

68,556

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章