庫存量計算欄位 希望庫存量當為0值時, 就不顯示出該筆整列資料

edited 十月 2013 in 資料庫
各位先進好

小弟有個SQL 庫存量計算欄位 希望庫存量當為0值時, 就不顯示出該筆整列資料

以下為原始SQL語法跟結果, 不知道應該如何修改(如序號 35 39 41) .... 才能排除掉庫存量為0資料....?? 麻煩各位先進們不吝惜指導 !!

SELECT ROW_NUMBER() OVER( ORDER BY LC001) AS 序號, LC002 AS 年月, LC001 AS 品號, MB002 AS 品名, MB003 AS 規格,
SUM(((LC004 + LC006 + LC012 + LC014) - (LC008 + LC010 + LC016 + LC022 + LC024))) AS 庫存量,
MB014 AS 單位淨重, MB015 AS 重量單位, ROUND((MB057 / 700),2) AS 單位標準成本_NTD
FROM BUENO_VN..INVLC B1 INNER JOIN BUENO_VN..INVMB
ON LC001 = MB001
WHERE LC002 = '201307' AND MB017 LIKE 'B121%' -- AND LC001 = '10263A002101'
GROUP BY LC001, LC002, MB002, MB003, MB014, MB015, MB057
ORDER BY LC001, MB002

結果:
序號 年月 品號 品名 規格 庫存量 單位淨重 重量單位 單位標準成本_NTD
32 201307 C311722011000 F515 BODY 1" CF8M 1.000 1.700000 Kg 380.8000000000
33 201307 C311722012000 F515 BODY 2" CF8M 6.000 5.950000 Kg 1299.4800000000
34 201307 C311722013000 F515 BODY 3" CF8M 13.000 8.320000 Kg 1817.0900000000
35 201307 C311722013001 體 DP-112470-I R.2 T112471 F515 3" WCB 0.000 8.460000 Kg 9.3500000000
36 201307 C311722370601 F515 CAP 3/4" WCB 2.000 1.150000 Kg 184.1800000000
37 201307 C311722371500 F515 BODY 1 1/2" WCB 10.000 3.150000 Kg 486.8600000000
38 201307 C311722372001 F535 BODY 2" WCB 1.000 6.000000 Kg 927.3600000000
39 201307 C311722373000 F515 BODY 3" WCB 0.000 7.740000 Kg 1196.2900000000
40 201307 C311722373001 F535 BODY 3" WCB 6.000 12.500000 Kg 1932.0000000000
41 201307 C311722374000 T112473 體 F515 4" WCB DP-112472-I R.3 0.000 12.500000 Kg 1932.0000000000
42 201307 C311723012000 F515 CAP 2" CF8M 16.000 3.900000 Kg 851.7600000000


原始討論: http://twpug.net/x/modules/newbb/viewtopic.php?topic_id=7429

評論

  • edited 八月 2013
    如果切割成兩個查詢呢?

    # 先取出庫存量為 0 的序號,例如 35 39 41
    # 在原本 SQL 的 WHERE 加上 LC001 NOT IN (35, 39, 41)

    或者透過資料庫的 view 來進行操作?
  • edited 八月 2013
    我的看法是..這可能需要知道你的表結構
    網站開發用這種SQL..玩笑開大了,還好你這個例子不像是WEB應用
  • edited 十月 2013
    SELECT FD1 AS 序號, FD2 AS 年月, FD3 AS 品號, FD4 AS 品名, FD5 AS 規格,
    FD6 AS 庫存量,
    FD7 AS 單位淨重, FD8 AS 重量單位, FD9 AS 單位標準成本_NTD
    FROM
    (
    SELECT ROW_NUMBER() OVER( ORDER BY LC001) AS FD1, LC002 AS FD2, LC001 AS FD3, MB002 AS FD4, MB003 AS FD5,
    SUM(((LC004 + LC006 + LC012 + LC014) - (LC008 + LC010 + LC016 + LC022 + LC024))) AS FD6,
    MB014 AS FD7, MB015 AS FD8, ROUND((MB057 / 700),2) AS FD9

    FROM BUENO_VN..INVLC B1 INNER JOIN BUENO_VN..INVMB
    ON LC001 = MB001
    WHERE LC002 = '201307' AND MB017 LIKE 'B121%' -- AND LC001 = '10263A002101'
    GROUP BY LC001, LC002, MB002, MB003, MB014, MB015, MB057
    ORDER BY LC001, MB002
    ) AS TEMP
    WHERE FD6>0

    以上 SQL 未經測試,你測試看看吧
Sign In or Register to comment.