create table po (code varchar(5),date char(8),QTY INT) INSERT INTO PO SELECT 'A-1','20101210',2000 INSERT INTO PO SELECT 'A-2','20101205',1000CREATE TABLE SI (SYMB CHAR(1),SEQ INT,DATE CHAR(8),QTY INT) INSERT INTO SI SELECT 'A',1,'20101202',1000 INSERT INTO SI SELECT 'A',1,'20101203',500 INSERT INTO SI SELECT 'A',1,'20101204',200 INSERT INTO SI SELECT 'A',2,'20101205',300 INSERT INTO SI SELECT 'A',2,'20101206',200 INSERT INTO SI SELECT 'A',2,'20101207',600 SELECT * FROM PO select * from SI _________________________________________ --------找出大的插入数据向原表------------------------- WITH CTE AS ( SELECT SUM(SIQTY) OVER(PARTITION BY SICODE ) AS SUMQTY,SICODE,DATE FROM (SELECT SYMB +'-'+ CAST(SEQ AS CHAR(1)) AS SICODE,QTY AS SIQTY,DATE FROM SI) B), CTE_SECODE AS ( SELECT SUBSTRING(CTE.SICODE,1,1)AS SYMDB,SUBSTRING(CTE.SICODE,3,1) AS SEQ ,MIN(CTE.DATE)-1 AS DATE,(PO.QTY-CTE.SUMQTY) AS [VALUE] FROM CTE JOIN PO ON CTE.SICODE=PO.CODE WHERE PO.QTY>CTE.SUMQTY GROUP BY CTE.SICODE,PO.QTY,CTE.SUMQTY) INSERT INTO SI(SYMB,SEQ,DATE,QTY) SELECT SYMDB,SEQ,DATE,[VALUE] FROM CTE_SECODE--------找出小的更新原表数据------------------------- WITH CTE AS ( SELECT SUM(SIQTY) OVER(PARTITION BY SICODE ) AS SUMQTY,SICODE,DATE FROM (SELECT SYMB +'-'+ CAST(SEQ AS CHAR(1)) AS SICODE,QTY AS SIQTY,DATE FROM SI) B), CTE_SECODE AS ( SELECT SUBSTRING(CTE.SICODE,1,1)AS SYMDB,SUBSTRING(CTE.SICODE,3,1) AS SEQ ,CTE.DATE AS DATE,(PO.QTY-CTE.SUMQTY) AS [VALUE] FROM CTE JOIN PO ON CTE.SICODE=PO.CODE WHERE PO.QTY<CTE.SUMQTY GROUP BY CTE.SICODE,PO.QTY,CTE.SUMQTY,CTE.DATE), UPDAT AS (select top 1 SI.* , CTE_SECODE.[VALUE] from CTE_SECODE JOIN SI ON SI.SYMB=CTE_SECODE.SYMDB AND CTE_SECODE.SEQ=SI.SEQ AND SI.DATE=CTE_SECODE.DATE order by SI.qty ) UPDATE SI SET SI.QTY=STUFF(UPDAT.[VALUE],1,1,'') FROM UPDAT WHERE SI.DATE=UPDAT.DATE ------------------------------------------------------------------------------------ /*------------------------ SELECT * FROM SI ------------------------*/ SYMB SEQ DATE QTY ---- ----------- -------- ----------- A 1 20101202 1000 A 1 20101203 500 A 1 20101204 200 A 2 20101205 300 A 2 20101206 100 A 2 20101207 600 A 1 20101201 300(7 行受影响)
if object_id('tempdb.dbo.#a') is not null drop table #aif object_id('tempdb.dbo.#b') is not null drop table #b go create table #a (CODE varchar(3),DATE varchar(10),QTY dec(14,2)) insert into #a select 'A-1','20101201',2000 union all select 'A-2','20101205',1000 create table #b (SYMB varchar(1),SEQ varchar(1),DATE varchar(10),QTY dec(14,2)) insert into #b select 'A', 1,'20101202', 1000 union all select 'A', 1,'20101203', 500 union all select 'A', 1,'20101204', 200 union all select 'A', 2,'20101205', 300 union all select 'A', 2,'20101207', 600select * from #b union all select a.symb,a.seq,b.date,b.qty-a.qty qty from (select symb,seq,sum(qty) qty from #b group by symb,seq ) a, (select left(code,1) symb,right(code,1) seq,date,qty from #a) b where a.symb=b.symb and a.seq=b.seq order by symb, seq/*A 1 20101202 1000.00 A 1 20101203 500.00 A 1 20101204 200.00 A 1 20101201 300.00 A 2 20101205 100.00 A 2 20101205 300.00 A 2 20101207 600.00 */
然后将他们插入到一张新表3中去。
INSERT INTO PO SELECT 'A-1','20101210',2000
INSERT INTO PO SELECT 'A-2','20101205',1000CREATE TABLE SI (SYMB CHAR(1),SEQ INT,DATE CHAR(8),QTY INT)
INSERT INTO SI SELECT 'A',1,'20101202',1000
INSERT INTO SI SELECT 'A',1,'20101203',500
INSERT INTO SI SELECT 'A',1,'20101204',200
INSERT INTO SI SELECT 'A',2,'20101205',300
INSERT INTO SI SELECT 'A',2,'20101206',200
INSERT INTO SI SELECT 'A',2,'20101207',600
SELECT * FROM PO
select * from SI
_________________________________________
--------找出大的插入数据向原表-------------------------
WITH CTE AS (
SELECT SUM(SIQTY) OVER(PARTITION BY SICODE ) AS SUMQTY,SICODE,DATE
FROM (SELECT SYMB +'-'+ CAST(SEQ AS CHAR(1)) AS SICODE,QTY AS SIQTY,DATE FROM SI) B),
CTE_SECODE AS ( SELECT SUBSTRING(CTE.SICODE,1,1)AS SYMDB,SUBSTRING(CTE.SICODE,3,1) AS SEQ
,MIN(CTE.DATE)-1 AS DATE,(PO.QTY-CTE.SUMQTY) AS [VALUE] FROM CTE JOIN PO ON CTE.SICODE=PO.CODE WHERE PO.QTY>CTE.SUMQTY
GROUP BY CTE.SICODE,PO.QTY,CTE.SUMQTY)
INSERT INTO SI(SYMB,SEQ,DATE,QTY) SELECT SYMDB,SEQ,DATE,[VALUE] FROM CTE_SECODE--------找出小的更新原表数据-------------------------
WITH CTE AS (
SELECT SUM(SIQTY) OVER(PARTITION BY SICODE ) AS SUMQTY,SICODE,DATE
FROM (SELECT SYMB +'-'+ CAST(SEQ AS CHAR(1)) AS SICODE,QTY AS SIQTY,DATE FROM SI) B),
CTE_SECODE AS ( SELECT SUBSTRING(CTE.SICODE,1,1)AS SYMDB,SUBSTRING(CTE.SICODE,3,1) AS SEQ
,CTE.DATE AS DATE,(PO.QTY-CTE.SUMQTY) AS [VALUE] FROM CTE JOIN PO ON CTE.SICODE=PO.CODE WHERE PO.QTY<CTE.SUMQTY
GROUP BY CTE.SICODE,PO.QTY,CTE.SUMQTY,CTE.DATE),
UPDAT AS (select top 1 SI.* , CTE_SECODE.[VALUE] from CTE_SECODE JOIN SI ON SI.SYMB=CTE_SECODE.SYMDB AND CTE_SECODE.SEQ=SI.SEQ
AND SI.DATE=CTE_SECODE.DATE
order by SI.qty )
UPDATE SI SET SI.QTY=STUFF(UPDAT.[VALUE],1,1,'') FROM UPDAT WHERE SI.DATE=UPDAT.DATE
------------------------------------------------------------------------------------
/*------------------------
SELECT * FROM SI
------------------------*/
SYMB SEQ DATE QTY
---- ----------- -------- -----------
A 1 20101202 1000
A 1 20101203 500
A 1 20101204 200
A 2 20101205 300
A 2 20101206 100
A 2 20101207 600
A 1 20101201 300(7 行受影响)
go
create table #a (CODE varchar(3),DATE varchar(10),QTY dec(14,2))
insert into #a
select 'A-1','20101201',2000 union all
select 'A-2','20101205',1000 create table #b (SYMB varchar(1),SEQ varchar(1),DATE varchar(10),QTY dec(14,2))
insert into #b
select 'A', 1,'20101202', 1000 union all
select 'A', 1,'20101203', 500 union all
select 'A', 1,'20101204', 200 union all
select 'A', 2,'20101205', 300 union all
select 'A', 2,'20101207', 600select * from #b union all
select a.symb,a.seq,b.date,b.qty-a.qty qty
from (select symb,seq,sum(qty) qty from #b group by symb,seq ) a,
(select left(code,1) symb,right(code,1) seq,date,qty from #a) b
where a.symb=b.symb and a.seq=b.seq
order by symb, seq/*A 1 20101202 1000.00
A 1 20101203 500.00
A 1 20101204 200.00
A 1 20101201 300.00
A 2 20101205 100.00
A 2 20101205 300.00
A 2 20101207 600.00
*/