如果有人能帮我解答问题,我将不胜感激。
我有一张表,每种货币都包含CurrenciesAmounts
我的第一个问题是:

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

那么,我如何加入这两个查询呢?
提前谢谢!

最佳答案

试试这个:

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

关于mysql - 根据条件t-sql将表与自身连接,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/21279415/

10-12 04:54