none
SQL伴随时间约束的库存汇总查询怎么写啊? RRS feed

  • 问题

  • 表1 :

    项目(nvarchar),时间(datetime),入库数量(int),出库数量(int)

    内容:

    项目         时间          入库数量     出库数量     

      ...           ...                   ...              ...

      A        2010.10.1            1               0

      A        2010.10.2            0               1

      A        2010.10.3            5               0

      B        2010.10.1            3               0

      ...           ...                   ...              ...

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

    查询1:

    我想查询 2010.10.1 至 2010.10.2 之间的库存情况,并伴有'期初'和'期末'数量的汇总

    希望得到的结果

    项目     期初数量   本期入库    本期出库    期末数量

      ...           ...             ..              ...               ...

      A             0             1               1                0 (0+1-1)

      B             0             3               0                3 (0+3-0)

      ...           ...             ..              ...               ...

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

    查询2:

    我想查询 2010.10.2 至 2010.10.3 之间的库存情况,并伴有'期初'和'期末'数量的汇总

    希望得到的结果

    项目     期初数量   本期入库    本期出库    期末数量

      ...           ...             ..              ...               ...

      A             1             5               1                5 (1+5-1)

      B             3             0               0                3 (3+0-0)

      ...           ...             ..              ...               ...

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

     

    2010年11月17日 8:55

答案

  • 你好1

    我寫了一些SQL 應該能夠做到你想實現的

     

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

     

    DECLARE @Start DATETIME

    SET @Start = '20101002'

    DECLARE @End DATETIME 

    SET @End = '20101003'

    DECLARE @TEMP TABLE (

    项目 VARCHAR(50),

    Total INT

    )

    INSERT INTO @TEMP

    (项目)

    SELECt 项目

    FROM TEST 

    WHERE DOE <= @End 

    GROUP BY 项目

     

    UPDATE tt

    SET tt.期初数量= COALESCE(t.入库数量,0) - COALESCE(t.出库数量,0) 

    FROM @TEMP tt

    LEFT JOIN

    (SELECt 项目, SUM(入库数量) AS 入库数量, SUM(出库数量) As 出库数量

    FROM TEST 

    WHERE DOE < @Start

    GROUP BY 项目) As t

    ON t.项目= tt.项目

     

     

    SELECT tt.项目AS 项目, Total As 期初数量, COALESCE(SUM(入库数量),0) AS  本期入库, COALESCE(SUM(出库数量),0) As 本期出库,

     Total +COALESCE(SUM(入库数量),0) - COALESCE(SUM(出库数量),0) AS 期末数量

    FROM @TEMP  tt

    LEFT JOIN  

    (SELECt 项目, SUM(入库数量) AS 入库数量, SUM(出库数量) As 出库数量

    FROM TEST 

    WHERE DOE BETWEEN @Start AND @End 

    GROUP BY 项目) AS t

    ON tt.项目= t.项目

    GROUP BY tt.项目, tt. 期初数量

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

     

    我測試的VERSION

    ----------

     

    DECLARE @Start DATETIME

    SET @Start = '20101002'

    DECLARE @End DATETIME 

    SET @End = '20101003'

    DECLARE @TEMP TABLE (

    Project VARCHAR(50),

    Total INT

    )

    INSERT INTO @TEMP

    (Project)

    SELECt Project

    FROM TEST 

    WHERE DOE <= @End 

    GROUP BY Project 

     

    UPDATE tt

    SET tt.Total = COALESCE(t.Initial,0) -COALESCE(t.finish,0)

    FROM @TEMP tt

    LEFT JOIN

    (SELECt Project, SUM(Initial) AS Initial, SUM(finish) As Finished

    FROM TEST 

    WHERE DOE < @Start

    GROUP BY Project ) As t

    ON t.Project = tt.Project

     

     

    SELECT tt.Project, Total As Orig , COALESCE(SUM(Initial),0) AS Initial, COALESCE(SUM(finish),0) As Finished,

     Total +COALESCE(SUM(Initial),0) - COALESCE(SUM(finish),0) AS FinalTotal

    FROM @TEMP  tt

    LEFT JOIN  

    (SELECt Project, SUM(Initial) AS Initial, SUM(finish) As finish

    FROM TEST 

    WHERE DOE BETWEEN @Start AND @End 

    GROUP BY Project ) AS t

    ON tt.Project = t.Project 

    GROUP BY tt.Project , tt. Total

     

     

     

    -----------

    Please correct me if my concept is wrong

     


    Chi
    2010年11月17日 14:10

全部回复

  • 你好1

    我寫了一些SQL 應該能夠做到你想實現的

     

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

     

    DECLARE @Start DATETIME

    SET @Start = '20101002'

    DECLARE @End DATETIME 

    SET @End = '20101003'

    DECLARE @TEMP TABLE (

    项目 VARCHAR(50),

    Total INT

    )

    INSERT INTO @TEMP

    (项目)

    SELECt 项目

    FROM TEST 

    WHERE DOE <= @End 

    GROUP BY 项目

     

    UPDATE tt

    SET tt.期初数量= COALESCE(t.入库数量,0) - COALESCE(t.出库数量,0) 

    FROM @TEMP tt

    LEFT JOIN

    (SELECt 项目, SUM(入库数量) AS 入库数量, SUM(出库数量) As 出库数量

    FROM TEST 

    WHERE DOE < @Start

    GROUP BY 项目) As t

    ON t.项目= tt.项目

     

     

    SELECT tt.项目AS 项目, Total As 期初数量, COALESCE(SUM(入库数量),0) AS  本期入库, COALESCE(SUM(出库数量),0) As 本期出库,

     Total +COALESCE(SUM(入库数量),0) - COALESCE(SUM(出库数量),0) AS 期末数量

    FROM @TEMP  tt

    LEFT JOIN  

    (SELECt 项目, SUM(入库数量) AS 入库数量, SUM(出库数量) As 出库数量

    FROM TEST 

    WHERE DOE BETWEEN @Start AND @End 

    GROUP BY 项目) AS t

    ON tt.项目= t.项目

    GROUP BY tt.项目, tt. 期初数量

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

     

    我測試的VERSION

    ----------

     

    DECLARE @Start DATETIME

    SET @Start = '20101002'

    DECLARE @End DATETIME 

    SET @End = '20101003'

    DECLARE @TEMP TABLE (

    Project VARCHAR(50),

    Total INT

    )

    INSERT INTO @TEMP

    (Project)

    SELECt Project

    FROM TEST 

    WHERE DOE <= @End 

    GROUP BY Project 

     

    UPDATE tt

    SET tt.Total = COALESCE(t.Initial,0) -COALESCE(t.finish,0)

    FROM @TEMP tt

    LEFT JOIN

    (SELECt Project, SUM(Initial) AS Initial, SUM(finish) As Finished

    FROM TEST 

    WHERE DOE < @Start

    GROUP BY Project ) As t

    ON t.Project = tt.Project

     

     

    SELECT tt.Project, Total As Orig , COALESCE(SUM(Initial),0) AS Initial, COALESCE(SUM(finish),0) As Finished,

     Total +COALESCE(SUM(Initial),0) - COALESCE(SUM(finish),0) AS FinalTotal

    FROM @TEMP  tt

    LEFT JOIN  

    (SELECt Project, SUM(Initial) AS Initial, SUM(finish) As finish

    FROM TEST 

    WHERE DOE BETWEEN @Start AND @End 

    GROUP BY Project ) AS t

    ON tt.Project = t.Project 

    GROUP BY tt.Project , tt. Total

     

     

     

    -----------

    Please correct me if my concept is wrong

     


    Chi
    2010年11月17日 14:10
  • 实在是太感谢了!!!!!!

    这些代码我先吸收消化一下...

    2010年11月18日 8:47