mysql - 根据条件将表与自身连接 t-sql

Question

如果有人可以帮助我解决我的问题，我将不胜感激。我有一个包含每种货币的 Currencies 和 Amounts 的表格。我的第一个查询是:

select Currency,  SUM(Amount) amnt from [MyTable] 
where CODE in(410, 420) 
group by Currency

所以，我得到:

Currency | amnt
USD      | 15

我想显示我的表中的所有可用货币并将它们与此结果结合起来(这些货币的金额必须为空)

Currency | amnt
USD      | 15
EUR      | 
AED      |

我通过查询得到所有货币的列表:

select currency from [MyTable] group by currency

那么，我该如何连接这两个查询呢？

提前致谢!

Answer 1

试试这个:

SELECT a.Currency, SUM(CASE WHEN a.CODE IN (410, 420) THEN a.Amount ELSE 0 END) amnt 
FROM MyTable a 
GROUP BY a.Currency

或

SELECT A.Currency, SUM(B.Amount) amnt 
FROM (SELECT DISTINCT Currency FROM MyTable) A
LEFT JOIN MyTable B ON A.Currency = B.Currency AND B.CODE IN (410, 420)
GROUP BY A.Currency

或

SELECT A.Currency, SUM(B.Amount) amnt 
FROM (SELECT Currency FROM MyTable GROUP BY Currency) A
LEFT JOIN MyTable B ON A.Currency = B.Currency AND B.CODE IN (410, 420)
GROUP BY A.Currency