如何在SQL中根据同一表中的另一列设置列总和值的别名

Question

我有一个Accounts表，有列：

Acc_Id、Transaction_TypeId、Amount

我想得到的结果是当Transaction_TypeId = 1然后金额总和作为"总预付款"。

否则，当Transaction_typeId = 2时，金额总和为"总接收">

这是我的SQL查询：

SELECT SUM(Amount) AS 'Sum' , Transaction_TypeId INTO #temp1  FROM AccountDetailTable WHERE  Account_MasterId = 1 GROUP BY Transaction_TypeId
SELECT Sum as 'Total Advance' from #temp1  WHERE #temp1.Transaction_TypeId = 1;
SELECT Sum as 'Total Cash Receipts' FROM #temp1  WHERE #temp1.Transaction_TypeId = 2;
DROP TABLE #temp1;

但是此查询返回两个不同的结果集。如何获取相同结果集中的值？

Answer 1

使用CASE表达式：

SELECT  SUM(CASE WHEN Transaction_TypeId = 1 THEN somecolumn END) as [Total Advance],
        SUM(CASE WHEN Transaction_TypeId = 2 THEN somecolumn END) as [Total Cash Receipts]
FROM #temp1;

Answer 2

你应该像这样使用 CASE 表达式：

SELECT
       sum(case when #temp1.Transaction_TypeId = 1 then amount else 0 end) as 'Total Advance',
       sum(case when #temp1.Transaction_TypeId = 2 then amount else 0 end) as 'Total Cash Receipts'
FROM #temp1