CINSPECTCODE SOURCEAUTOID DDATE CTIME 入 出 结存
CPBJ20130701000005 1000000009 2013-7-1 0:00 19:26:28 2 0 0
CPBJ20130701000005 1000000008 2013-7-1 0:00 19:26:28 2 0 0
CPBJ20130701000005 1000000010 2013-7-1 0:00 19:26:28 30 0 0
CPBJ20130701000005 1000000009 2013-7-3 0:00 16:51:12 0 2 0
CPBJ20130701000005 1000000010 2013-7-3 0:00 16:52:22 0 2 0
CPBJ20130701000005 1000000010 2013-7-5 0:00 20:40:17 0 2 0
CPBJ20130701000005 1000000010 2013-7-7 0:00 14:18:02 0 26 0
转换
CPBJ20130701000005 1000000009 2013-7-1 0:00 19:26:28 2 0 2
CPBJ20130701000005 1000000009 2013-7-3 0:00 16:51:12 0 2 0
CPBJ20130701000005 1000000008 2013-7-1 0:00 19:26:28 2 0 2
CPBJ20130701000005 1000000010 2013-7-1 0:00 19:26:28 30 0 32
CPBJ20130701000005 1000000010 2013-7-3 0:00 16:52:22 0 2 30
CPBJ20130701000005 1000000010 2013-7-5 0:00 20:40:17 0 2 28
CPBJ20130701000005 1000000010 2013-7-7 0:00 14:18:02 0 26 2
-- 上面的格式转换成下面的,主要是在结存列上操作,不会弄 求大大,哥哥帮帮忙吧,会结贴!!!相信我!!!
(
SELECT 'CPBJ20130701000005',1000000009,'2013-7-1 0:00','19:26:28',2, 0, 0 UNION all
SELECT 'CPBJ20130701000005',1000000008,'2013-7-1 0:00','19:26:28',2, 0, 0 UNION all
SELECT 'CPBJ20130701000005',1000000010,'2013-7-1 0:00','19:26:28',30, 0, 0 UNION all
SELECT 'CPBJ20130701000005',1000000009,'2013-7-3 0:00','16:51:12',0, 2, 0 UNION all
SELECT 'CPBJ20130701000005',1000000010,'2013-7-3 0:00','16:52:22',0, 2, 0 UNION all
SELECT 'CPBJ20130701000005',1000000010,'2013-7-5 0:00','20:40:17',0, 2, 0 UNION all
SELECT 'CPBJ20130701000005',1000000010,'2013-7-7 0:00','14:18:02',0, 26, 0
)
,a2 AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY CINSPECTCODE ORDER BY DDATE,CTIME) re
FROM a1
)
SELECT CINSPECTCODE,SOURCEAUTOID,DDATE,CTIME,in_qty,out_qty,
(SELECT SUM(in_qty-out_qty) FROM a2 WHERE re<=a.re) end_qty
FROM a2 a
ORDER BY re
r,c,
jc=CASE WHEN r=0 THEN isnull((select sum(r) from aka_temptable where sourceautoid=t.sourceautoid and ddate<t.ddate),r)-c
ELSE isnull((select sum(jc) from aka_temptable where sourceautoid=t.sourceautoid and ddate<t.ddate),r)+c
END
from aka_temptable t
order by sourceautoid,ddate
艾玛,我自己写的还是有点不对
create table u01
(CINSPECTCODE varchar(20), SOURCEAUTOID varchar(20),
DDATE varchar(20), CTIME varchar(10), 入 int, 出 int, 结存 int)insert into u01
select 'CPBJ20130701000005', '1000000009', '2013-7-1 0:00', '19:26:28', 2, 0, 0 union all
select 'CPBJ20130701000005', '1000000008', '2013-7-1 0:00', '19:26:28', 2, 0, 0 union all
select 'CPBJ20130701000005', '1000000010', '2013-7-1 0:00', '19:26:28', 30, 0, 0 union all
select 'CPBJ20130701000005', '1000000009', '2013-7-3 0:00', '16:51:12', 0, 2, 0 union all
select 'CPBJ20130701000005', '1000000010', '2013-7-3 0:00', '16:52:22', 0, 2, 0 union all
select 'CPBJ20130701000005', '1000000010', '2013-7-5 0:00', '20:40:17', 0, 2, 0 union all
select 'CPBJ20130701000005', '1000000010', '2013-7-7 0:00', '14:18:02', 0, 26, 0
with t as
(select *,row_number() over(partition by SOURCEAUTOID
order by cast((replace(DDATE,'0:00','')+CTIME) as datetime)) 'rn'
from u01
)
select a.CINSPECTCODE,a.SOURCEAUTOID,a.DDATE,a.CTIME,a.入,a.出,
(select sum(b.入)-sum(b.出) from t b
where b.SOURCEAUTOID=a.SOURCEAUTOID and b.rn<=a.rn) '结存'
from t a
order by a.SOURCEAUTOID,a.rn
/*
CINSPECTCODE SOURCEAUTOID DDATE CTIME 入 出 结存
-------------------- -------------------- -------------------- ---------- ----------- ----------- -----------
CPBJ20130701000005 1000000008 2013-7-1 0:00 19:26:28 2 0 2
CPBJ20130701000005 1000000009 2013-7-1 0:00 19:26:28 2 0 2
CPBJ20130701000005 1000000009 2013-7-3 0:00 16:51:12 0 2 0
CPBJ20130701000005 1000000010 2013-7-1 0:00 19:26:28 30 0 30
CPBJ20130701000005 1000000010 2013-7-3 0:00 16:52:22 0 2 28
CPBJ20130701000005 1000000010 2013-7-5 0:00 20:40:17 0 2 26
CPBJ20130701000005 1000000010 2013-7-7 0:00 14:18:02 0 26 0(7 row(s) affected)
*/
WITH a1 (CINSPECTCODE,SOURCEAUTOID,DDATE,CTIME,in_qty,out_qty,end_qty) AS
(
SELECT 'CPBJ20130701000005',1000000009,'2013-7-1 0:00','19:26:28',2, 0, 0 UNION all
SELECT 'CPBJ20130701000005',1000000008,'2013-7-1 0:00','19:26:28',2, 0, 0 UNION all
SELECT 'CPBJ20130701000005',1000000010,'2013-7-1 0:00','19:26:28',30, 0, 0 UNION all
SELECT 'CPBJ20130701000005',1000000009,'2013-7-3 0:00','16:51:12',0, 2, 0 UNION all
SELECT 'CPBJ20130701000005',1000000010,'2013-7-3 0:00','16:52:22',0, 2, 0 UNION all
SELECT 'CPBJ20130701000005',1000000010,'2013-7-5 0:00','20:40:17',0, 2, 0 UNION all
SELECT 'CPBJ20130701000005',1000000010,'2013-7-7 0:00','14:18:02',0, 26, 0
)
,a2 AS
(
SELECT *,ROW_NUMBER() OVER (ORDER BY GETDATE()) re
FROM a1
)
,a3 AS
(
SELECT SOURCEAUTOID,MIN(re) re FROM a2 GROUP BY SOURCEAUTOID
)
,a4 AS
(
SELECT a2.*,ROW_NUMBER() OVER (ORDER BY a3.re,a2.re) re2
FROM a2
INNER JOIN a3 ON a2.SOURCEAUTOID=a3.SOURCEAUTOID
)SELECT CINSPECTCODE,SOURCEAUTOID,DDATE,CTIME,in_qty,out_qty,
(SELECT SUM(in_qty-out_qty) FROM a4 WHERE re2<=a.re2) end_qty
FROM a4 a
ORDER BY re2
大牛,排序还是日期排序,第一个挺好,但是我表示看不懂! 换了其他的数据,就不行了。CPBJ20130701000005 1000000009 2013-07-03 00:00:00.000 16:51:12 0.000000 2 0
CPBJ20130701000005 1000000010 2013-07-03 00:00:00.000 16:52:22 0.000000 2 0
CPBJ20130701000005 1000000010 2013-07-05 00:00:00.000 20:40:17 0.000000 2 0
CPBJ20130701000005 1000000010 2013-07-07 00:00:00.000 14:18:02 0.000000 26 0
CPBJ20130701000014 1000000032 2013-07-07 00:00:00.000 14:19:13 0.000000 26 0
CPBJ20130701000014 1000000032 2013-07-13 00:00:00.000 09:03:53 0.000000 1 0
CPBJ20130701000014 1000000032 2013-07-13 00:00:00.000 17:41:58 0.000000 1 0
CPBJ20130701000014 1000000032 2013-07-13 00:00:00.000 17:54:20 0.000000 1 0
CPBJ20130706000180 1000000032 2013-07-18 00:00:00.000 09:10:57 0.000000 5 0
CPBJ20130701000005 1000000009 2013-07-01 00:00:00.000 19:26:28 2.000000 0 0
CPBJ20130701000005 1000000010 2013-07-01 00:00:00.000 19:26:28 30.000000 0 0
CPBJ20130701000014 1000000032 2013-07-01 00:00:00.000 19:34:21 29.000000 0 0
CPBJ20130706000180 1000000032 2013-07-06 00:00:00.000 09:06:23 5.000000 0 0
这样的数据一开始 结存就有36
CPBJ20130701000005 1000000009 2013-07-01 00:00:00.000 19:26:28 2 0 2
CPBJ20130701000005 1000000009 2013-07-03 00:00:00.000 16:51:12 0 2 0
CPBJ20130701000005 1000000010 2013-07-01 00:00:00.000 19:26:28 30 0 30
CPBJ20130701000005 1000000010 2013-07-03 00:00:00.000 16:52:22 0 2 28
CPBJ20130701000005 1000000010 2013-07-05 00:00:00.000 20:40:17 0 2 26
CPBJ20130701000005 1000000010 2013-07-07 00:00:00.000 14:18:02 0 26 0
CPBJ20130701000014 1000000032 2013-07-01 00:00:00.000 19:34:21 29 0 29
哭了 大牛请看目前的数据,呜呜,我想7-1号的排在前面
3楼的代码,是按SOURCEAUTOID+日期排序的.
;with t as
(select *,row_number() over(partition by SOURCEAUTOID
order by cast((replace(DDATE,'0:00','')+CTIME) as datetime)) 'rn'
from #u01
)
select a.CINSPECTCODE,a.SOURCEAUTOID,a.DDATE,a.CTIME,a.入,a.出,
(select sum(b.入)-sum(b.出) from t b
where b.SOURCEAUTOID=a.SOURCEAUTOID and b.rn<=a.rn) '结存'
from t a
order by a.rn,a.SOURCEAUTOID感觉3楼写的 能满足你的需求 只需要排序给换下顺序就行...