两个表对比数据，汇总到第三个表，求存储过程算法

IEEE_China 2012-04-28 03:22:43 




--申请表



[申请表]的字段：aid自动增，姓名，数值，年，时间段



aid	姓名	数值	年	时间段



1	张三	100	2012	1.1-1.31

2	李四	150	2012	1.1-1.31

3	王五	130	2012	1.1-1.31

4	张三	120	2012	2.1-2.29

5	李四	110	2012	2.1-2.29

6	王五	120	2012	2.1-2.29





--审批表

[审批表]的字段：bid自动增，姓名，数值，年，时间段



bid	姓名	数值	年	时间段



1	张三	90	2012	1.1-1.31

2	李四	120	2012	1.1-1.31

3	王五	100	2012	1.1-1.31

4	张三	100	2012	2.1-2.29









--[汇总表]的字段：cid自动增，姓名，核定值，备注。







汇总数据，要求计算核定值，计算备注里的差值。





---计算



核定值计算方式：

如果上月审批数值不为空，则：

   核定值=上月申请数值+（前月审批数值-前月申请数值）+（上月审批数值-上月申请数值）

如果上月审批数值为空，则：

   核定值=上月申请数值+（前月审批数值-前月申请数值）

差值计算方式：

   前月差值=前月审批数值-前月申请数值

   上月月差值=上月审批数值-上月申请数值





注：假定当前为3月，上月为2月，前月为1月。







----示例



现在举例说明 核定值的计算方式。

假设当前时间为2012.3.1,汇总2月所有的[申请表]和[审批表]的数据到[汇总表]。

假设变量a,b,c,d，汇总张三的数据，



a=[申请表]中 张三 在 时间段1.1-1.31 的数值(100)

b=[审批表]中 张三 在 时间段1.1-1.31 的数值(90)

c=[申请表]中 张三 在 时间段2.1-2.29  的数值(120)

d=[审批表]中 张三 在 时间段2.1-2.29  的数值(100)



1月份张三的差值为：1月差值=b-a

2月份张三的差值为：2月差值=d-c



2月份张三的核定值为：核定值=c+(b-a)+(d-c)



其他人的汇总也是这样算。



因审批表2012.2.1-2.29李四王五没有审批，所以2月没有差值。



汇总表

cid	姓名	核定值	备注

1	张三	90	2012.1.1-1.31申请100，差值-10；2012.2.1-2.29申请120，差值-20；

2	李四	80	2012.1.1-1.31申请150，差值-30；2012.2.1-2.29申请110；

3	王五	90	2012.1.1-1.31申请130，差值-30；2012.2.1-2.29申请120；







-----问题



问：使用存储过程，如何汇总[申请表]和[审批表]的数据到[汇总表]？
...全文
345 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
叶子 2012-05-01
  • 打赏
  • 举报
 回复 


if object_id('申请表') is not null drop table 申请表

create table 申请表 

(aid int identity(1,1),姓名 varchar(4),数值 int,年 int,时间段 varchar(8),月份 int)

insert into 申请表

select '张三',100,2012,'1.1-1.31',1 union all

select '李四',150,2012,'1.1-1.31',1 union all

select '王五',130,2012,'1.1-1.31',1 union all

select '张三',120,2012,'2.1-2.29',2 union all

select '李四',110,2012,'2.1-2.29',2 union all

select '王五',120,2012,'2.1-2.29',2



if object_id('审批表') is not null drop table 审批表

create table 审批表 (bid int,姓名 varchar(4),数值 int,年 int,时间段 varchar(8),月份 int)

insert into 审批表

select 1,'张三',90,2012,'1.1-1.31',1 union all

select 2,'李四',120,2012,'1.1-1.31',1 union all

select 3,'王五',100,2012,'1.1-1.31',1 union all

select 4,'张三',100,2012,'2.1-2.29',2



--假设当前月是2012-03月

declare @date datetime set @date='2012-03-01'

;with maco as

( 

select a.*,b.数值 as 数值1 from 申请表 a left join

审批表 b on a.姓名=b.姓名 and a.年=b.年

and a.时间段=b.时间段 where 

datediff(m,cast(ltrim(a.年)+'-'+ltrim(a.月份)+'-01' as datetime),@date)<3

)

, t as

(

select *,

datediff(m,cast(ltrim(年)+'-'+ltrim(月份)+'-01' as datetime),@date) as 月类型

from maco

)



select 姓名,

核定值=(case when (select 数值1 from t where 月类型=1 and 姓名=m.姓名) is not null

then (select sum(数值1) from t where 姓名=m.姓名)-(select sum(数值) from t where 姓名=m.姓名

and 月类型=2) else 

(select sum(isnull(数值,0)) from t where 姓名=m.姓名

and 月类型=1)+(select sum(isnull(数值1,0)) from t where 姓名=m.姓名

and 月类型=2)-(select sum(isnull(数值,0)) from t where 姓名=m.姓名

and 月类型=2)

end),

备注=(select ltrim(年)+'.'+时间段+'申请'+ltrim(数值) from t where 月类型=2 and 姓名=m.姓名)+

'，差值'+ltrim((select 数值1 from t where 姓名=m.姓名

and 月类型=2)-(select 数值 from t where 姓名=m.姓名 and 月类型=2))+'；'+

(select ltrim(年)+'.'+时间段+'申请'+ltrim(数值) from t where 月类型=1 and 姓名=m.姓名)

+isnull('，差值'+ltrim((select 数值1 from t where 姓名=m.姓名

and 月类型=1)-(select 数值 from t where 姓名=m.姓名 and 月类型=1)),'')+'；'

from t m group by 姓名 

order by charindex(姓名,'张三,李四,王五')



--order by 这个的排序就是为了结果和你的结果排序一致。

/*

姓名   核定值         备注

---- ----------- ------------------------------------------------------------------------------------------------------------------

张三   90          2012.1.1-1.31申请100，差值-10；2012.2.1-2.29申请120，差值-20；

李四   80          2012.1.1-1.31申请150，差值-30；2012.2.1-2.29申请110；

王五   90          2012.1.1-1.31申请130，差值-30；2012.2.1-2.29申请120；

*/
叶子 2012-05-01
  • 打赏
  • 举报
 回复 


if object_id('申请表') is not null drop table 申请表

create table 申请表 

(aid int identity(1,1),姓名 varchar(4),数值 int,年 int,时间段 varchar(8),月份 int)

insert into 申请表

select '张三',100,2012,'1.1-1.31',1 union all

select '李四',150,2012,'1.1-1.31',1 union all

select '王五',130,2012,'1.1-1.31',1 union all

select '张三',120,2012,'2.1-2.29',2 union all

select '李四',110,2012,'2.1-2.29',2 union all

select '王五',120,2012,'2.1-2.29',2



if object_id('审批表') is not null drop table 审批表

create table 审批表 (bid int,姓名 varchar(4),数值 int,年 int,时间段 varchar(8),月份 int)

insert into 审批表

select 1,'张三',90,2012,'1.1-1.31',1 union all

select 2,'李四',120,2012,'1.1-1.31',1 union all

select 3,'王五',100,2012,'1.1-1.31',1 union all

select 4,'张三',100,2012,'2.1-2.29',2



--假设当前月是2012-03月

declare @date datetime set @date='2012-03-01'

;with maco as

( 

select a.*,b.数值 as 数值1 from 申请表 a left join

审批表 b on a.姓名=b.姓名 and a.年=b.年

and a.时间段=b.时间段 where 

datediff(m,cast(ltrim(a.年)+'-'+ltrim(a.月份)+'-01' as datetime),@date)<3

)

, t as

(

select *,

datediff(m,cast(ltrim(年)+'-'+ltrim(月份)+'-01' as datetime),@date) as 月类型

from maco

)



select 姓名,

核定值=(case when (select 数值1 from t where 月类型=1 and 姓名=m.姓名) is not null

then (select sum(数值1) from t where 姓名=m.姓名)-(select sum(数值) from t where 姓名=m.姓名

and 月类型=2) else 

(select sum(isnull(数值,0)) from t where 姓名=m.姓名

and 月类型=2)+(select sum(isnull(数值1,0)) from t where 姓名=m.姓名

and 月类型=1)-(select sum(isnull(数值,0)) from t where 姓名=m.姓名

and 月类型=1)

end),

备注=(select ltrim(年)+'.'+时间段+'申请'+ltrim(数值) from t where 月类型=2 and 姓名=m.姓名)+

'，差值'+ltrim((select 数值1 from t where 姓名=m.姓名

and 月类型=2)-(select 数值 from t where 姓名=m.姓名 and 月类型=2))+'；'+

(select ltrim(年)+'.'+时间段+'申请'+ltrim(数值) from t where 月类型=1 and 姓名=m.姓名)

+isnull('，差值'+ltrim((select 数值1 from t where 姓名=m.姓名

and 月类型=1)-(select 数值 from t where 姓名=m.姓名 and 月类型=1)),'')+'；'

 from t m group by 姓名

/*

姓名   核定值         备注

---- ----------- -----------------------------------------------------------

李四   40          2012.1.1-1.31申请150，差值-30；2012.2.1-2.29申请110；

王五   10          2012.1.1-1.31申请130，差值-30；2012.2.1-2.29申请120；

张三   90          2012.1.1-1.31申请100，差值-10；2012.2.1-2.29申请120，差值-20；

*/



--核定值我算的还是不对，我再修正一下。
叶子 2012-05-01
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 的回复:]
引用 8 楼 的回复:
SQL code

if object_id('申请表') is not null drop table 申请表
create table 申请表
(aid int identity(1,1),姓名 varchar(4),数值 int,年 int,时间段 varchar(8),月份 int)
insert into 申请表
select '张三',100,20……
[/Quote]

datediff(m,cast(ltrim(a.年)+'-'+ltrim(a.月份)+'-01' as datetime),@date)<3

这个条件是限制时间的，月差小于3，就是最近两个月的，没有问题的。
IEEE_China 2012-05-01
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 的回复:]
SQL code

if object_id('申请表') is not null drop table 申请表
create table 申请表
(aid int identity(1,1),姓名 varchar(4),数值 int,年 int,时间段 varchar(8),月份 int)
insert into 申请表
select '张三',100,2012,'1.1-1.31……
[/Quote]
叶子，每个月都能统计前2个月的数据吗？
就像现在是5月了，统计4月份的申请、审批数据;

到10月份，统计9月份的数据，并且在备注里列出8月和9月的数据吗？
IEEE_China 2012-04-28
  • 打赏
  • 举报
 回复
回复叶子，
如果上月没有审批，则上月审批数值为空，按照申请数值计算。


如果上月审批了，核定值=c+(b-a)+(d-c)



如果上月没有审批，核定值=c+(b-a)


IEEE_China 2012-04-28
  • 打赏
  • 举报
 回复
回复叶子，






示例数据只是测试用的，实际用到的数据肯能会出现负数。



计算方式就是下边这样的：

核定值计算方式：

如果上月审批数值不为空，则：

   核定值=上月申请数值+（前月审批数值-前月申请数值）+（上月审批数值-上月申请数值）

如果上月审批数值为空，则：

   核定值=上月申请数值+（前月审批数值-前月申请数值）

差值计算方式：

   前月差值=前月审批数值-前月申请数值

   上月月差值=上月审批数值-上月申请数值





汇总格式按照这个格式：

汇总表

cid    姓名    核定值    备注

1    张三    90    2012.1.1-1.31申请100，差值-10；2012.2.1-2.29申请120，差值-20；

2    李四    80    2012.1.1-1.31申请150，差值-30；2012.2.1-2.29申请110；

3    王五    90    2012.1.1-1.31申请130，差值-30；2012.2.1-2.29申请120；
IEEE_China 2012-04-28
  • 打赏
  • 举报
 回复
负数没关系

叶子 2012-04-28
  • 打赏
  • 举报
 回复
2月份张三的核定值为：核定值=c+(b-a)+(d-c)

这个就等于b+d-a

这样的话 李四和王五 就是负数了...
叶子 2012-04-28
  • 打赏
  • 举报
 回复 


--第一问就算不对结果

if object_id('申请表') is not null drop table 申请表

create table 申请表 

(aid int identity(1,1),姓名 varchar(4),数值 int,年 int,时间段 varchar(8))

insert into 申请表

select '张三',100,2012,'1.1-1.31' union all

select '李四',150,2012,'1.1-1.31' union all

select '王五',130,2012,'1.1-1.31' union all

select '张三',120,2012,'2.1-2.29' union all

select '李四',110,2012,'2.1-2.29' union all

select '王五',120,2012,'2.1-2.29'



if object_id('审批表') is not null drop table 审批表

create table 审批表 (bid int,姓名 varchar(4),数值 int,年 int,时间段 varchar(8))

insert into 审批表

select 1,'张三',90,2012,'1.1-1.31' union all

select 2,'李四',120,2012,'1.1-1.31' union all

select 3,'王五',100,2012,'1.1-1.31' union all

select 4,'张三',100,2012,'2.1-2.29'



select 核定值=(select sum(isnull(数值,0)) from 审批表 t where 姓名=a.姓名)-(

select top 1 数值 from 申请表 m where  姓名=a.姓名

),

* from 申请表 a left join 

审批表 b on a.姓名=b.姓名 and a.年=b.年

and a.时间段=b.时间段

where a.时间段='1.1-1.31'

/*

核定值         aid         姓名   数值          年           时间段      bid         姓名   数值          年           时间段

----------- ----------- ---- ----------- ----------- -------- ----------- ---- ----------- ----------- --------

90          1           张三   100         2012        1.1-1.31 1           张三   90          2012        1.1-1.31

-30         2           李四   150         2012        1.1-1.31 2           李四   120         2012        1.1-1.31

-30         3           王五   130         2012        1.1-1.31 3           王五   100         2012        1.1-1.31

*/
IEEE_China 2012-04-28
  • 打赏
  • 举报
 回复
高手们，怎么汇总上个月的数据？


--汇总格式

cid    姓名    核定值    备注

1    张三    90    2012.1.1-1.31申请100，差值-10；2012.2.1-2.29申请120，差值-20；

2    李四    80    2012.1.1-1.31申请150，差值-30；2012.2.1-2.29申请110；

3    王五    90    2012.1.1-1.31申请130，差值-30；2012.2.1-2.29申请120；
《数据结构》知识点汇总+算法代码总结【全】
期末复习和备考408均可使用第一章：绪论（不在考研大纲但很重要）第二章：线性表第三章：栈和队列第四章：串第五章：树与二叉树第六章：图第七章：查找 第一章：绪论（不在考研大纲但很重要） 数据结构三要素：逻辑...
【超全汇总】学习数据结构与算法，计算机基础知识，看这篇就够了
由于文章有点多，并且发的文章也不是一个系列一个系列发的，不过我的文章大部分都是围绕着 数据结构 + 算法 + 计算机网络 + 操作系统 + Linux + 数据库 这几个方面发的，为了方便大家阅读，我整理了一波。...
数据结构与算法——知识点总结
本文包含数据结构与算法主要的基本知识点，便于知识的梳理和回顾。 如需详细了解具体知识点请自行结合课本或者网上查阅。 目录 1、概述 2、线性表 3、栈 4、队列 5、串 6、多维数组和广义表 7、树和二叉树 ...
数据结构与算法之美
数据结构和算法解决的是如何更省、更快地存储和处理数据的问题. 数据结构和算法是相辅相成的。数据结构是为算法服务的，算法要作用在特定的数据结构之上。 因此，我们无法孤立数据结构来讲算法，也无法孤立...
数据结构算法题汇总
2. ，算法的四个基本特征是？ 说明：可行性、确定性、有穷性和输入输出。 3. 算法一般都可以用哪几种控制结构组合而成？ 答案：顺序、选择、循环。 4. 算法的时间复杂度是指？ 答案：算法执行过程中所需要的基本...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章