我有 3 個表。第一個表是學生。第二個是 student_detail，最后一個是 special_codes。
student table
studentname | invoiceno |tax |invoiceamount
Paul 10 500 1950
Georghe 20 1000 6850
Mary 30 1500 1900
Messy 40 2000 7050
studentdetail
invoiceno | code | product | amount
10 101 pencil 100
10 102 rubber 350
10 103 bag 1500
20 108 wheel 5000
20 109 tv 1500
20 110 ps 300
20 111 mouse 50
30 103 bag 1500
30 105 keyboard 400
40 111 mouse 50
40 112 car 7000
我可以像這樣加入這兩個表并獲得結果表
select s.studentname,s.tax,s.invoiceamount,st.product,sum(st.amount) from student s, studentdetail st
where s.invoiceno = st.invoiceno
group by
s.studentname,
s.tax,
s.invoiceamount,
st.product
result table
studentname tax invoiceamount product amount
Paul 500 1950 bag 1500
Paul 500 1950 pencil 100
Paul 500 1950 rubber 350
Messy 2000 7050 car 7000
Messy 2000 7050 mouse 50
Mary 1500 1900 bag 1500
Mary 1500 1900 keyboard 400
Georghe 1000 6850 mouse 50
Georghe 1000 6850 ps 300
Georghe 1000 6850 tv 1500
Georghe 1000 6850 wheel 5000
最后一張表是特殊代碼。它只包含一列稱為代碼
specialcodes table
code
101
102
113
104
105
110
111
我想要做的是查找studentdetail表并在特殊代碼中找到相同的代碼。然后對金額值求和并將總和作為另一列寫入結果表。結果表應該是這樣的
result table(final)
studentname tax invoiceamount product amount taxexclude
Paul 500 1950 bag 1500 450
Paul 500 1950 pencil 100 450
Paul 500 1950 rubber 350 450
Messy 2000 7050 car 7000 50
Messy 2000 7050 mouse 50 50
Mary 1500 1900 bag 1500 400
Mary 1500 1900 keyboard 400 400
Georghe 1000 6850 mouse 50 350
Georghe 1000 6850 ps 300 350
Georghe 1000 6850 tv 1500 350
Georghe 1000 6850 wheel 5000 350
uj5u.com熱心網友回復：
您可以使用分析函式而不是GROUP BY聚合：
select s.studentname,
s.tax,
invoiceamount,
SUM(d.amount) OVER (PARTITION BY s.invoiceno) AS inv_amt_calc,
d.product,
d.amount,
SUM(CASE WHEN c.code IS NOT NULL THEN d.amount END)
OVER (PARTITION BY s.invoiceno) AS taxexclude
from student s
INNER JOIN studentdetail d
ON s.invoiceno = d.invoiceno
LEFT OUTER JOIN specialcodes c
ON (c.code = d.code)
注意：您可以（并且應該）從studentdetails表格中計算發票金額，而不是復制表格中的資料student并違反第三范式。
其中，對于您的樣本資料，輸出：
學生姓名 稅 發票金額 INV_AMT_CALC 產品 數量 免稅 保羅 500 1950 1950 橡膠 350 450 保羅 500 1950 1950 鉛筆 100 450 保羅 500 1950 1950 包 1500 450 喬治 1000 6850 6850 電視 1500 350 喬治 1000 6850 6850 車輪 5000 350 喬治 1000 6850 6850 ps 300 350 喬治 1000 6850 6850 老鼠 50 350 瑪麗 1500 1900 1900 包 1500 400 瑪麗 1500 1900 1900 鍵盤 400 400 亂 2000 7050 7050 老鼠 50 50 亂 2000 7050 7050 車 7000 50
如果你真的想要一個使用GROUP BYthen的版本：
SELECT s.studentname,
s.tax,
s.invoiceamount,
SUM(d.amount) OVER (PARTITION BY s.invoiceno) AS inv_amt_calc,
d.product,
d.amount,
t.taxexclude
FROM student s
INNER JOIN studentdetail d
ON s.invoiceno = d.invoiceno
LEFT OUTER JOIN (
SELECT invoiceno,
SUM(amount) AS taxexclude
FROM studentdetail
WHERE code IN (SELECT code FROM specialcodes)
GROUP BY
invoiceno
) t
ON s.invoiceno = t.invoiceno;
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/419556.html
標籤：