none
群組內累減問題? RRS feed

  • 問題


  • 您好:
    想請問,若有一個明細表td ,另有一個參考表 ta,若想由td群組後,
    由每一群的第一筆,對到ta,取ta.st,然後td群組內自行累減,
    這該要如何做呢?


    ---明細表
    create table td(
    TITLE_ID varchar(4),
    DATEX  varchar(8),
    DC varchar(1),
    D_AMT int,
    C_AMT int
    )

    insert table td('1101','20120801','D',1000,200);
    insert table td('1101','20120802','D',2000,100);
    insert table td('1101','20120803','D',1000,100);
    insert table td('1101','20120804','D',1000,200);

    insert table td('1102','20120801','D',1000,200);
    insert table td('1102','20120802','D',500,100);
    insert table td('1102','20120803','D',500,300);

    insert table td('1103','20120801','C',0,100);
    insert table td('1103','20120802','C',0,200);
    insert table td('1103','20120803','C',0,300);


    create table ta(
    TITLE_ID varchar(4),
    DC varchar(1),
    ST int
    )
    insert table td('1101','D',6000);
    insert table td('1102','D',8000);
    insert table td('1103','C',4000);


    我想得到如下的結果,其中累減該如何處理呢?
    若DC=D,則 ts.ST+D_AMT-C_AMT
      DC=C,則 ts.ST-D_AMT+C_AMT

    TITLE_ID, DATEX    , DC ,D_AMT,C_AMT,ta.ST,累減
    1001    , 20120801 , D  ,1000 ,200  ,6000 ,6000+1000-200=6800  
    1001    , 20120802 , D  ,2000 ,100  ,6000 ,6800+2000-100=8700
    1001    , 20120803 , D  ,3000 ,100  ,6000 ,8700+1000-100=9600
    1001    , 20120804 , D  ,4000 ,200  ,6000 ,9600+1000-200=10400

    1002    , 20120801 , D  ,1000 ,200  ,8000 ,8000+1000-200=8800
    1002    , 20120802 , D  , 500 ,100  ,8000 ,8800+500-100=9200
    1002    , 20120803 , D  , 500 ,300  ,8000 ,9200+500-200=9500

    1003    , 20120801 , c  , 0   ,100  ,4000 ,4000-0+100=4100
    1003    , 20120802 , c  , 0   ,200  ,4000 ,4100-0+200=4300
    1003    , 20120803 , c  , 0   ,300  ,4000 ,4300-0+300=4600
    2012年10月18日 上午 12:36

解答

  • 請參考看看 ~~
    USE tempdb
    GO
    
    IF OBJECT_ID('td') IS NOT NULL
    	DROP TABLE td
    
    IF OBJECT_ID('ta') IS NOT NULL
    	DROP TABLE ta
    
    CREATE TABLE td
    	(
    		TITLE_ID varchar(4), 
    		DATEX  varchar(8),
    		DC varchar(1),
    		D_AMT int,
    		C_AMT int
    	)
    
    INSERT INTO td VALUES('1101','20120801','D',1000,200)
    INSERT INTO td VALUES('1101','20120802','D',2000,100)
    INSERT INTO td VALUES('1101','20120803','D',1000,100)
    INSERT INTO td VALUES('1101','20120804','D',1000,200)
    INSERT INTO td VALUES('1102','20120801','D',1000,200)
    INSERT INTO td VALUES('1102','20120802','D',500,100)
    INSERT INTO td VALUES('1102','20120803','D',500,300)
    INSERT INTO td VALUES('1103','20120801','C',0,100)
    INSERT INTO td VALUES('1103','20120802','C',0,200)
    INSERT INTO td VALUES('1103','20120803','C',0,300)
    
    CREATE TABLE ta
    	(
    		TITLE_ID varchar(4),
    		DC varchar(1),
    		ST int
    	)
    INSERT INTO ta VALUES('1101','D',6000)
    INSERT INTO ta VALUES('1102','D',8000)
    INSERT INTO ta VALUES('1103','C',4000)
    
    -- 以上為測試資料
    -- 下述語法只適用於 SQL Server 2012
    
    SELECT T.* ,
      SUM
    	(
    		IIF(ROWNO = 1 , ST , 0) + T.D_AMT + T.C_AMT
    	)
      OVER 
        (
          PARTITION BY T.TITLE_ID
          ORDER BY T.DateX
          ROWS UNBOUNDED PRECEDING -- 2012 新語法
        ) AS Balance		
    FROM
    	(
    		SELECT 
    			D.TITLE_ID , 
    			D.DATEX ,
    			D.DC ,
    			D.D_AMT ,
    			IIF( D.DC = 'D' , D.C_AMT * -1 , D.C_AMT) AS C_AMT ,
    			A.ST , 
    			ROW_NUMBER() OVER (PARTITION BY D.TITLE_ID ORDER BY DateX) AS ROWNO
    		FROM td AS D
    			JOIN ta AS A ON D.TITLE_ID = A.TITLE_ID 
    								AND D.DC = A.DC
    	) AS T

    Google 累計加總 或 runing total 這兩個關鍵字也可以找到參考資料 ~~


    • 已標示為解答 softballnow 2012年10月19日 上午 05:05
    2012年10月18日 上午 02:48

所有回覆

  • 請參考看看 ~~
    USE tempdb
    GO
    
    IF OBJECT_ID('td') IS NOT NULL
    	DROP TABLE td
    
    IF OBJECT_ID('ta') IS NOT NULL
    	DROP TABLE ta
    
    CREATE TABLE td
    	(
    		TITLE_ID varchar(4), 
    		DATEX  varchar(8),
    		DC varchar(1),
    		D_AMT int,
    		C_AMT int
    	)
    
    INSERT INTO td VALUES('1101','20120801','D',1000,200)
    INSERT INTO td VALUES('1101','20120802','D',2000,100)
    INSERT INTO td VALUES('1101','20120803','D',1000,100)
    INSERT INTO td VALUES('1101','20120804','D',1000,200)
    INSERT INTO td VALUES('1102','20120801','D',1000,200)
    INSERT INTO td VALUES('1102','20120802','D',500,100)
    INSERT INTO td VALUES('1102','20120803','D',500,300)
    INSERT INTO td VALUES('1103','20120801','C',0,100)
    INSERT INTO td VALUES('1103','20120802','C',0,200)
    INSERT INTO td VALUES('1103','20120803','C',0,300)
    
    CREATE TABLE ta
    	(
    		TITLE_ID varchar(4),
    		DC varchar(1),
    		ST int
    	)
    INSERT INTO ta VALUES('1101','D',6000)
    INSERT INTO ta VALUES('1102','D',8000)
    INSERT INTO ta VALUES('1103','C',4000)
    
    -- 以上為測試資料
    -- 下述語法只適用於 SQL Server 2012
    
    SELECT T.* ,
      SUM
    	(
    		IIF(ROWNO = 1 , ST , 0) + T.D_AMT + T.C_AMT
    	)
      OVER 
        (
          PARTITION BY T.TITLE_ID
          ORDER BY T.DateX
          ROWS UNBOUNDED PRECEDING -- 2012 新語法
        ) AS Balance		
    FROM
    	(
    		SELECT 
    			D.TITLE_ID , 
    			D.DATEX ,
    			D.DC ,
    			D.D_AMT ,
    			IIF( D.DC = 'D' , D.C_AMT * -1 , D.C_AMT) AS C_AMT ,
    			A.ST , 
    			ROW_NUMBER() OVER (PARTITION BY D.TITLE_ID ORDER BY DateX) AS ROWNO
    		FROM td AS D
    			JOIN ta AS A ON D.TITLE_ID = A.TITLE_ID 
    								AND D.DC = A.DC
    	) AS T

    Google 累計加總 或 runing total 這兩個關鍵字也可以找到參考資料 ~~


    • 已標示為解答 softballnow 2012年10月19日 上午 05:05
    2012年10月18日 上午 02:48
  • 您好:

    謝謝, 於2005中,似乎無法使用SUM(..)   OVER(PARTATION A   ORDER BY b,c)

    有加 ORDER BY b,c 就無法過....

    2012年10月18日 上午 06:57