難搞的程式-阿維 │愛情公寓交友網站與聊天室

還沒登入愛情公寓嗎？還沒加入愛情公寓嗎？馬上進入公寓和 10,548,567 個住戶交朋友～

紅包抽抽樂！小資變土豪！

素人也能成為明日之星！

移除此區廣告請加入VIP

檔案狀態:

住戶編號:1939249

阿維的日記本

快速選單

睡太飽《前一篇

後一篇》精藝求精

回應

給他日記貼紙

給他愛的鼓勵

檢舉

篇名: 難搞的程式
作者: 阿維	日期: 2012.12.06　　天氣: 　心情:

SELECT     Stocklist.tcsno_ AS 'TCSNO', Stocklist.translocal_, Part.type_ AS '型式', Part.spec_ AS '尺寸',
                          (SELECT     SUM(transqty_) AS Expr1
                            FROM          Stocklist AS a
                            WHERE      (tcsno_ = stocklist.tcsno_) AND (LEFT(transtype_, 1) = 'A') AND (translocal_ = Stocklist.translocal_)) AS '入庫數', ISNULL
                          ((SELECT     SUM(transqty_) AS Expr1
                              FROM         Stocklist AS c
                              WHERE     (stocklist.tcsno_ = tcsno_) AND (translocal_ = Stocklist.translocal_) AND (LEFT(transtype_, 1) IN ('B', 'C'))), 0) AS '出庫數', ISNULL
                          ((SELECT     SUM(transqty_) AS Expr1
                              FROM         Stocklist AS c
                              WHERE     (stocklist.tcsno_ = tcsno_) AND (LEFT(transtype_, 1) = 'A') AND (translocal_ = Stocklist.translocal_)), 0) - ISNULL
                          ((SELECT     SUM(transqty_) AS Expr1
                              FROM         Stocklist AS c
                              WHERE     (stocklist.tcsno_ = tcsno_) AND (translocal_ = Stocklist.translocal_) AND (LEFT(transtype_, 1) IN ('B', 'C'))), 0) AS '當前庫存',
                      ISNULL
                          ((SELECT     SUM(transqty_) AS Expr1
                              FROM         Stocklist AS c
                              WHERE     (stocklist.tcsno_ = tcsno_) AND (transtype_ IN ('CA'))), 0) AS '預庫數', ISNULL
                          ((SELECT     TOP (1) translocal_
                              FROM         Stocklist AS c
                              WHERE     (stocklist.tcsno_ = tcsno_) AND (transtype_ IN ('CA'))), 0) AS '預庫架位', ISNULL
                          ((SELECT     COUNT(translocal_) AS Expr1
                              FROM         Stocklist AS c
                              WHERE     (stocklist.tcsno_ = tcsno_) AND (transtype_ IN ('CA'))), 0) AS '筆'
FROM         Stocklist INNER JOIN
                      Part ON Stocklist.tcsno_ = Part.partno_
GROUP BY Stocklist.tcsno_, Stocklist.translocal_, Part.type_, Part.spec_
HAVING      (ISNULL
                          ((SELECT     SUM(transqty_) AS Expr1
                              FROM         Stocklist AS c
                              WHERE     (stocklist.tcsno_ = tcsno_) AND (LEFT(transtype_, 1) = 'A') AND (translocal_ = Stocklist.translocal_)), 0) - ISNULL
                          ((SELECT     SUM(transqty_) AS Expr1
                              FROM         Stocklist AS c
                              WHERE     (stocklist.tcsno_ = tcsno_) AND (translocal_ = Stocklist.translocal_) AND (LEFT(transtype_, 1) IN ('B', 'C'))), 0) > 0)

------
這隻難搞的程式，怎一直寫不出來呢 (秀出庫存2個架位以上的清單，以更讓倉管人員把分散多架位的產品集中在一個架位)
希望今天下班前應該能想出語法。

標籤:

瀏覽次數:241 人氣指數:2841 累積鼓勵:130

切換閱讀模式

回應

給他日記貼紙

給他愛的鼓勵

檢舉

給本文愛的鼓勵：