时间戳

首页 > 解决方案 > 将 SQL 查询放在 case 表达式中

sql - 将 SQL 查询放在 case 表达式中

问题描述

我想放

SELECT DISTINCT ResidentGroup 
FROM Credits.ProductsPurposesBalanceGroups 
WHERE BalanceTypeID = 1`

在case表达式中，因为表Credits.ProductsPurposesBalanceGroups总是在变化，我需要检查合规性。

当我以这种方式运行下面的代码时，我收到此错误：

无法对包含聚合或子查询的表达式执行聚合函数

如何避免这种情况，或者也许有另一种方法可以做到这一点？

select 
    p.TypeName as 'Purpose', 
    sum((case 
            when tr.CurrencyID = 417 
                 and acc_credit.BalanceGroup in (select distinct ResidentGroup 
                                                 from Credits.ProductsPurposesBalanceGroups 
                                                 where BalanceTypeID = 1) 
               then tr.SumV 
               else 0 
         end)) as 'Pay417', 
    sum((case 
            when tr.CurrencyID = 840 
                 and acc_credit.BalanceGroup in (select distinct ResidentGroup 
                                                 from Credits.ProductsPurposesBalanceGroups 
                                                 where BalanceTypeID = 2) 
               then tr.SumV 
               else 0 
         end)) as 'Pay840', 
      -- and it goes that way until BalanceTypeID = 16"""

from 
    dbo.Transactions as tr 
inner join  
    dbo.Accounts as acc_credit on (tr.CreditAccountID = acc_credit.AccountNo)
group by 
    p.TypeName 
order by 
    p.TypeName

标签： sqlsql-servertsql

解决方案

一种解决方法是先选择 lineitem，然后在第二遍中聚合。以 CTE 为例：

--Lineitem
;WITH CTE AS (
select 
    p.TypeName as 'Purpose', 
         case 
            when tr.CurrencyID = 417 
                 and acc_credit.BalanceGroup in (select distinct ResidentGroup 
                                                 from Credits.ProductsPurposesBalanceGroups 
                                                 where BalanceTypeID = 1) 
               then tr.SumV 
               else 0 
         end as 'Pay417', 
         case 
            when tr.CurrencyID = 840 
                 and acc_credit.BalanceGroup in (select distinct ResidentGroup 
                                                 from Credits.ProductsPurposesBalanceGroups 
                                                 where BalanceTypeID = 2) 
               then tr.SumV 
               else 0 
         end as 'Pay840'
      -- and it goes that way until BalanceTypeID = 16"""

from 
    dbo.Transactions as tr 
inner join  
    dbo.Accounts as acc_credit on (tr.CreditAccountID = acc_credit.AccountNo)

.
.
.
) --End of CTE

--Aggregate lineitem
SELECT Purpose
      ,SUM(Pay417) AS [Pay417]
      ,SUM(Pay840) AS [Pay840]
  FROM CTE

group by 
    Purpose
order by 
    Purpose

推荐阅读