我有兩個來自兩個視圖的查詢
查詢一：
SELECT sum(cancels) as cancelcount,codeprogram
FROM CanceliTems
WHERE date_save BETWEEN '1/21/2022' AND
'2/20/2022' and usercode = 8066
GROUP By codeprogram
結果是：
| 取消計數 | 代碼程式 |
|---|---|
| 7 | 3001 |
| 7 | 3002 |
| 2 | 3006 |
查詢 2：
SELECT SUM(sellcount) sellcount,codeprogram,price
FROM sells
WHERE date_save BETWEEN '1/21/2022' AND '2/20/2022' and usercode = 8066
GROUP By codeprogram,price
結果是：
| 銷售計數 | 代碼程式 | 價錢 |
|---|---|---|
| 27 | 3001 | 10000 |
| 25 | 3000 | 20000 |
| 9 | 3006 | 25000 |
| 3 | 3011 | 15000 |
| 8 | 3008 | 11000 |
| 55 | 3002 | 50000 |
如何加入兩個視圖，然后從銷售數量中減去取消數量，最后將數量乘以價格，在指定的日期范圍內？
在每個視圖中，我都有欄位 date_save (datetime)，它由 getdate() 和用戶代碼填充。
finalcount = (sellcount - cancelcount) * 價格
我希望最終輸出如下：
| 最終計數 | 代碼程式 | 價錢 |
|---|---|---|
| 20 | 3001 | 200000 |
| 25 | 3000 | 500000 |
| 7 | 3006 | 175000 |
| 3 | 3011 | 45000 |
| 8 | 3008 | 88000 |
| 47 | 3002 | 2350000 |
謝謝
uj5u.com熱心網友回復：
您可以嘗試使用兩個子查詢的一種方法OUTER JOIN
SELECT sellcount - ISNULL(cancelcount,0) finalcount,
t1.codeprogram,
t1.price
FROM (
SELECT SUM(sellcount) sellcount,codeprogram,price
FROM sells
WHERE date_save BETWEEN '1/21/2022' AND '2/20/2022' and usercode = 8066
GROUP By codeprogram,price
) t1
LEFT JOIN (
SELECT sum(cancels) as cancelcount,codeprogram
FROM CanceliTems
WHERE date_save BETWEEN '1/21/2022' AND '2/20/2022' and usercode = 8066
GROUP By codeprogram
) t2 ON t1.codeprogram = t2.codeprogram
從您的查詢中，您可以重寫如下，使用UNION ALL組合sells&CanceliTems并制作一個標志來表示 1 是正數，2 是負數，然后使用條件聚合函式。
SELECT SUM(CASE WHEN flag = 1 THEN sellcount ELSE - sellcount END),codeprogram,SUM(price) price
FROM (
SELECT sellcount,codeprogram,price , 1 flag
FROM sells
WHERE date_save BETWEEN '1/21/2022' AND '2/20/2022' and usercode = 8066
UNION ALL
SELECT cancels,codeprogram,0 , 2
FROM CanceliTems
WHERE date_save BETWEEN '1/21/2022' AND '2/20/2022' and usercode = 8066
) t1
GROUP BY codeprogram
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/439336.html