我正在使用 postgresql 資料庫并有 2 個表。它們具有相同型別的列。
Table1
id sale material
1 10 m1
2 2 m2
3 3 m1
4 40 m3
5 50 m2
Table2
id name material
1 5 m3
2 15 m1
3 20 m1
4 20 m3
5 10 m1
所以我想得到一個加入的sql，比如：
material table1_sale table2_sale
m1 13 45
m2 52 0
m3 40 25
使用查詢
select t1.material , sum(t1.sale), sum(t2.sale)
from table1 t1
join table2 t2 on t1.material = t2.material
group by t1.material, t2.material
但是得到錯誤的結果。我怎樣才能做到這一點？
uj5u.com熱心網友回復：
您當前的查詢匯總了錯誤的結果，因為它匯總了此查詢的值：
select t1.material , t1.sale, t2.sale
from table1 t1
join table2 t2 on t1.material = t2.material
通過一些步驟，在 dbfiddle 中顯示，這是最終查詢：
SELECT material, SUM(table1_sale), SUM(table2_Sale)
from (
select material, sum(sale) as table1_sale, 0 as table2_sale from table1 group by material
union all
select material, 0, sum(sale) from table2 group by material
) x
group by material
order by material;
見：DBFIDDLE
uj5u.com熱心網友回復：
sale一種可能的方法是在加入之前聚合material。
With A As
(Select material, Sum(sale) As table1_sale From table1 Group by material),
B As
(Select material, Sum(sale) As table2_sale From table2 Group by material)
Select Coalesce(t1.material,t2.material) As material,
Coalesce(t1.table1_sale,0) As table1_sale,
Coalesce(t2.table2_sale,0) As table2_sale
From A As t1 Full Join B As t2 On (t1.material=t2.material)
Order by material
輸出：
| 材料 | table1_sale | table2_sale |
|---|---|---|
| 米1 | 13 | 45 |
| 平方米 | 52 | 0 |
| 立方米 | 40 | 25 |
db<>小提琴
uj5u.com熱心網友回復：
是的，加入前先分組。
我用 WITH 子句做到了，因為我更喜歡它的可讀性......
WITH
-- your input, don't use in final query ..
table1(id,sale,material) AS (
SELECT 1,10,'m1'
UNION ALL SELECT 2,2,'m2'
UNION ALL SELECT 3,3,'m1'
UNION ALL SELECT 4,40,'m3'
UNION ALL SELECT 5,50,'m2'
)
,
table2(id,sale,material) AS (
SELECT 1,5,'m3'
UNION ALL SELECT 2,15,'m1'
UNION ALL SELECT 3,20,'m1'
UNION ALL SELECT 4,20,'m3'
UNION ALL SELECT 5,10,'m1'
)
-- end of your input, replace following comma with "WITH"
,
t1grp AS (
SELECT
material
, SUM(sale) AS t1sale
FROM table1
GROUP BY material
)
,
t2grp AS (
SELECT
material
, SUM(sale) AS t2sale
FROM table2
GROUP BY material
)
SELECT
t1grp.material
, t1sale
, NVL(t2sale,0)
FROM t1grp LEFT JOIN t2grp USING(material)
ORDER BY 1;
-- out material | t1sale | NVL
-- out ---------- -------- -----
-- out m1 | 13 | 45
-- out m2 | 52 | 0
-- out m3 | 40 | 25
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/451188.html
標籤：sql PostgreSQL