sql - Postgres - 查询从多个表中选择字段作为列

Question

我有以下表格

表 1:产品

id  name  
1   Bread  
2   Bun  
3   Cake  

表2:费用项目

product| quantity  
1 |      100  
2 |      150  
3 |      180  
1 |      25  
2 |      30  

表三:收入项目

product| quantity  
1 |      100  
2 |      150  
3 |      180  
1 |      25  
2 |      30  

现在我想要这样的结果

product   |  sum of quantity of expenseitem | sum of quantity of income item  
1         |  125 |  125  
2         |  180 |  180  
3         |  180 |  180

得到这个结果的查询是什么？
谢谢

Answer 1

您可以尝试在子查询中使用 UNION ALL 并在聚合函数中设置条件

架构(PostgreSQL v9.6)

CREATE TABLE Product(
    id int,
    name varchar(50)
);


INSERT INTO Product VALUES (1,'Bread');  
INSERT INTO Product VALUES (2,'Bun');  
INSERT INTO Product VALUES (3,'Cake');  

CREATE TABLE ExpenseItems(
    product int,
    quantity int
);


INSERT INTO ExpenseItems VALUES (1,100);  
INSERT INTO ExpenseItems VALUES (2,150);  
INSERT INTO ExpenseItems VALUES (3,180);  
INSERT INTO ExpenseItems VALUES (1,25);  
INSERT INTO ExpenseItems VALUES (2,30);  


CREATE TABLE IncomeItems(
    product int,
    quantity int
);


INSERT INTO IncomeItems VALUES (1,100);  
INSERT INTO IncomeItems VALUES (2,150);  
INSERT INTO IncomeItems VALUES (3,180);  
INSERT INTO IncomeItems VALUES (1,25);  
INSERT INTO IncomeItems VALUES (2,30);  

---

查询#1

SELECT p.id,
      SUM(CASE WHEN grp = 1 THEN quantity END) SUMExpenseItems,
      SUM(CASE WHEN grp = 2 THEN quantity END) SUMIncomeItems
FROM (
    SELECT product, quantity,1 grp 
    FROM ExpenseItems
    UNION ALL
    SELECT product, quantity,2 
    FROM IncomeItems
) t1 JOIN Product p on p.id = t1.product
GROUP BY p.id;

| id  | sumexpenseitems | sumincomeitems |
| --- | --------------- | -------------- |
| 1   | 125             | 125            |
| 2   | 180             | 180            |
| 3   | 180             | 180            |

---

View on DB Fiddle