T1:编号 时间 金额 进程
1001 2011-01-01 12:31:23 100 0
1001 2011-01-02 12:31:23 200 0
1001 2011-01-03 12:31:23 300 2
1001 2011-01-04 12:31:23 400 0
1002 2011-01-02 12:31:23 100 0
1002 2011-01-03 12:31:23 200 1
1002 2011-01-04 12:31:23 300 0
1002 2011-01-05 12:31:23 400 0
1002 2011-01-08 12:31:23 500 0
1002 2011-01-10 12:31:23 600 0
1003 2011-01-21 12:31:23 200 0
1003 2011-01-22 12:31:23 200 0
1003 2011-01-23 12:31:23 400 0
1003 2011-01-24 12:31:23 600 10
1003 2011-01-25 12:31:23 800 0
结果编号 前2天金额合计 进程不为0的金额合计 进程不为0以后金额合计(不含不为0的那天)
1001 300 300 400
1002 100 200 1800
1003 400 600 800注意的地方:如果进程不为0的前面天数不足2天,但不足的合计(如果一天都没有,那就是0,有一天就算一天),超过2天得按第一和第二天算
1001 2011-01-01 12:31:23 100 0
1001 2011-01-02 12:31:23 200 0
1001 2011-01-03 12:31:23 300 2
1001 2011-01-04 12:31:23 400 0
1002 2011-01-02 12:31:23 100 0
1002 2011-01-03 12:31:23 200 1
1002 2011-01-04 12:31:23 300 0
1002 2011-01-05 12:31:23 400 0
1002 2011-01-08 12:31:23 500 0
1002 2011-01-10 12:31:23 600 0
1003 2011-01-21 12:31:23 200 0
1003 2011-01-22 12:31:23 200 0
1003 2011-01-23 12:31:23 400 0
1003 2011-01-24 12:31:23 600 10
1003 2011-01-25 12:31:23 800 0
结果编号 前2天金额合计 进程不为0的金额合计 进程不为0以后金额合计(不含不为0的那天)
1001 300 300 400
1002 100 200 1800
1003 400 600 800注意的地方:如果进程不为0的前面天数不足2天,但不足的合计(如果一天都没有,那就是0,有一天就算一天),超过2天得按第一和第二天算
解决方案 »
- 一个表中检查将要写入的记录是否与原有记录重复共三个字段
- 关于成绩排名问题,急
- 关于搜索功能
- 企业管理器如何对各个角色有关表操作权限生成教本?
- 分母是0,在SQL中要如何处理
- 存储过程从一个表里的记录数量多条循环插入,急
- MS SQL Server 问题,怎样查询删除某一条记录的一列的值?
- 求把dbf导入SQL的方法,要具体的步骤,要是只会说个点导入就行了,那就请不用进来看了。给帮我搞定的100分。只给一个人。
- 求救高手:怎样提高对数据的查询统计效率!急!!!!!!!!!!!!!
- SQL Server 的分布式事务处理
- 请教 查询结果中的一列加上自增编号再插入到一个表中
- 求解高人-------如何生成播放日程的SQL语句.
declare @T1 table (编号 int,时间 datetime,金额 int,进程 int)
insert into @T1
select 1001,'2011-01-01 12:31:23',100,0 union all
select 1001,'2011-01-02 12:31:23',200,0 union all
select 1001,'2011-01-03 12:31:23',300,2 union all
select 1001,'2011-01-04 12:31:23',400,0 union all
select 1002,'2011-01-02 12:31:23',100,0 union all
select 1002,'2011-01-03 12:31:23',200,1 union all
select 1002,'2011-01-04 12:31:23',300,0 union all
select 1002,'2011-01-05 12:31:23',400,0 union all
select 1002,'2011-01-08 12:31:23',500,0 union all
select 1002,'2011-01-10 12:31:23',600,0 union all
select 1003,'2011-01-21 12:31:23',200,0 union all
select 1003,'2011-01-22 12:31:23',200,0 union all
select 1003,'2011-01-23 12:31:23',400,0 union all
select 1003,'2011-01-24 12:31:23',600,10 union all
select 1003,'2011-01-25 12:31:23',800,0;with maco as
(
select row_number() over (partition by 编号 order by 时间) as id,
* from @T1
)select
编号,
(select sum(金额) from maco where 编号=a.编号 and id<3) as 前2天金额合计,
(select sum(金额) from maco where 编号=a.编号 and 进程<>0) as 进程不为0的金额合计,
(select sum(金额) from maco
where 编号=a.编号 and 时间>
(select min(时间) from maco where 编号=a.编号 and 进程<>0)) as 进程不为0以后金额合计
from maco a group by 编号
/*
编号 前2天金额合计 进程不为0的金额合计 进程不为0以后金额合计
----------- ----------- ----------- -----------
1001 300 300 400
1002 300 200 1800
1003 400 600 800
*/
insert into @T1
select 1001,'2011-01-01 12:31:23',100,0 union all
select 1001,'2011-01-02 12:31:23',200,0 union all
select 1001,'2011-01-03 12:31:23',300,2 union all
select 1001,'2011-01-04 12:31:23',400,0 union all
select 1002,'2011-01-02 12:31:23',100,0 union all
select 1002,'2011-01-03 12:31:23',200,1 union all
select 1002,'2011-01-04 12:31:23',300,0 union all
select 1002,'2011-01-05 12:31:23',400,0 union all
select 1002,'2011-01-08 12:31:23',500,0 union all
select 1002,'2011-01-10 12:31:23',600,0 union all
select 1003,'2011-01-21 12:31:23',200,0 union all
select 1003,'2011-01-22 12:31:23',200,0 union all
select 1003,'2011-01-23 12:31:23',400,0 union all
select 1003,'2011-01-24 12:31:23',600,10 union all
select 1003,'2011-01-25 12:31:23',800,0;with maco as
(
select row_number() over (partition by 编号 order by 时间) as id,
* from @T1
)select
编号,
(select sum(金额) from maco where 编号=a.编号 and id<3
and 时间<(select min(时间) from maco where 编号=a.编号 and 进程<>0)) as 前2天金额合计,
(select sum(金额) from maco where 编号=a.编号 and 进程<>0) as 进程不为0的金额合计,
(select sum(金额) from maco
where 编号=a.编号 and 时间>
(select min(时间) from maco where 编号=a.编号 and 进程<>0)) as 进程不为0以后金额合计
from maco a group by 编号/*
编号 前2天金额合计 进程不为0的金额合计 进程不为0以后金额合计
----------- ----------- ----------- -----------
1001 300 300 400
1002 100 200 1800
1003 400 600 800
*/