各位先進請指教 資料庫-MS SQL 2000 產品存貨檔(inve) cdate- cust- item- qty 200101 0000 aaaaa 1 200101 0000 bbbbb 2 200101 1111 aaaaa 2 200101 1111 bbbbb 2 200102 0000 aaaaa 2 200102 0000 bbbbb 1 200102 1111 aaaaa 1 200102 1111 bbbbb 2 產品成本檔(cost) cdate- item- cost 200101 aaaaa 100 200101 bbbbb 200 200102 aaaaa 80 當要抓取200102的總成本時, 彙總產品存貨檔各貨號的數量 並抓取產品成本檔的成本相乘,但如貨號:bbbbb在200102無資料時 需抓取產品成本檔最接近200102時的成本 以(200102)為例 item- qty- cost aaaaa 3 240 bbbbb 3 600 sql command 應如何下?先感謝您的回覆

SELECT i.item,SUM(i.qty)qty,SUM(i.qty * c.cost)cost FROM inve i LEFT JOIN cost c ON i.item=c.item AND i.cdate=c.cdate WHERE i.cdate='200201' GROUP BY i.item ORDER BY i.item 這是我之前的寫法 問題在於當產品成本檔同年月無資料時 成本就不會計算 另外考慮過先產生暫存檔,過濾出產品成本檔 最接近年月,又不超過指定年月的成本,再來JOIN 但命令一樣不會下,煩請各位先進指導一下,謝謝

hi, 這樣下看看. 用了一道 subquery: select item, sum(qty), cost=sum(qty)*(select top 1 cost from cost where item=x.item and cdate <= '200102' order by cdate desc) from invc x where cdate='200102' group by item

感謝timhuang 試過了確實可以 另外請教若是以(cust)作分類時,又該如何下 cust- qty- cost aaaaa 3 240 bbbbb 3 600 改了半天還是試不出來,謝謝

所謂以 cust 分類的意思是? 不是很清楚耶. 是指定 cust 嗎? 那 cost 也是要找最近的日期嗎?

抱歉不是說的很清楚 應該是說以cust作群組 類似這樣 select cust, sum(qty), cost=sum(qty)*(select top 1 cost from cost where item=x.item and cdate <= '200102' order by cdate desc) from invc x where cdate='200102' group by cust 不過這樣在子查詢的部分就會有問題 再麻煩您謝謝

當然是不行的. 因為子查詢中要用的欄位在主查詢有 group by 子句時, 都必須是出現的, 否則不能成立. 若你要 group by cust , 那你的 subquery 子句是要以什麼為條件呢? 也就是 cost 中是沒有 cust 的, 那麼 cost 是指該 cust 所有的 item sum 起來的 cost 嗎? 其實下 query 不是那麼的困難, 只是要先確認好邏輯和條件, 這樣就容易多了.

再次謝謝timhuang 翻了二本SQL的書,所有子查詢的範例都是下在WHERE之後 看了您的回文才知子查詢也可用在WHERE之前 SELECT的欄位一直以為要像這樣 SELECT cust,SUM(qty)as qty,SUM(qty * cost) as cost 不知道也可以這樣 SELECT cust,SUM(qty)as qty,cost=SUM(qty) * ??? 回到主題 產品存貨檔(inve) cdate- cust- item- qty 200101 0000 aaaaa 1 200101 0000 bbbbb 2 200101 1111 aaaaa 2 200101 1111 bbbbb 2 200102 0000 aaaaa 2 200102 0000 bbbbb 1 200102 1111 aaaaa 2 200102 1111 bbbbb 2 產品成本檔(cost) cdate- item- cost 200101 aaaaa 100 200101 bbbbb 200 200102 aaaaa 80 當要抓取200102的總成本時, 彙總產品存貨檔各(cust)的數量 並抓取產品成本檔的成本相乘,但如貨號:bbbbb在200102無資料時 需抓取產品成本檔最接近200102時的成本 以(200102)為例 cust- qty- cost 0000 3 360 ----->(aaaaa)2*80 (bbbbb)1*200 1111 4 560 ----->(aaaaa)2*80 (bbbbb)2*200 應該也就是您說的意思 謝謝您的回應讓我受益良多

原來是這樣啊. 那就得要這樣: select cust, sum(amount) amount, sum(cost) cost from (select cust, item, sum(qty) amount, cost=sum(qty)*(select top 1 cost from cost where item=x.item and cdate <= '200102' order by cdate desc) from invc x where cdate='200102' group by cust, item) b group by cust 再多加一層 query, 因為其中最內層的 query 必須會用到 item 的欄位, 所以必須在第二層中為外顯欄位, 所以必須再用一層(一共三層來解決這個問題)!

原來如此單純,多加一層 query 就可以了 又多領會一種用法 謝謝timhuang