不能使用聚合或子查询。

Question

我试图对这个案例表达式进行分组，但不幸的是，我遇到了一个错误。

select da.order_id, osl.itemid, sum(case when osl.sku = '00005' then 0 when osl.sku = '00006' then 0 else osl.price * sil.quantity end) merchcost, sum(sil.merchUnitCost * sil.quantity) cogs, sum(sil.quantity) quantity,
    CASE
    WHEN EXISTS (SELECT * FROM fcat.dbo.subscriptionskus a where a.itemId = osl.itemid) then 1
    WHEN EXISTS (SELECT * FROM foam.dbo.subscriptionprogram b where b.orderStateLineId = osl.orderStateLine_id ) then 1
    else 0
    END as isAdmin,

    CASE
    WHEN EXISTS (SELECT * FROM fcat.dbo.subscriptionskus a where a.itemId = osl.itemid) then CAST('subscriptionskus' as varchar(MAX))
    WHEN EXISTS (SELECT * FROM foam.dbo.subscriptionprogram b where b.orderStateLineId = osl.orderStateLine_id ) then CAST('subscriptionprogram' as varchar(MAX))
    else 'NotListed'
    END as SubTable

from #dispatchAmounts da 
inner join orderstates os on os.order_id = da.order_id
inner join orderstatelines osl on osl.orderState_id = os.orderState_id
inner join shippingIntentLines sil on sil.orderStateLine_id = osl.orderStateLine_id and da.shippingIntent_nbr = sil.shippingIntent_nbr
where da.code = 'merch' and sil.quantity > 0
group by da.order_id, osl.itemid, CASE
    WHEN EXISTS (SELECT * FROM fcat.dbo.subscriptionskus a where a.itemId = osl.itemid) then 1
    WHEN EXISTS (SELECT * FROM foam.dbo.subscriptionprogram b where b.orderStateLineId = osl.orderStateLine_id ) then 1
    else 0
    END,CASE
    WHEN EXISTS (SELECT * FROM fcat.dbo.subscriptionskus a where a.itemId = osl.itemid) then CAST('subscriptionskus' as varchar(MAX))
    WHEN EXISTS (SELECT * FROM foam.dbo.subscriptionprogram b where b.orderStateLineId = osl.orderStateLine_id ) then CAST('subscriptionprogram' as varchar(MAX))
    else 'NotListed'
    END

错误：

不能在用于group by子句的组列表的表达式中使用聚合或子查询。

不管怎样，我能做到这一点吗？

Answer 1

我有几个建议。在case语句中使用select *，但“*”引用的列不在group by子句中。添加它们将是解决问题的糟糕方法。相反，使用‘select 1’，看看会发生什么。

第二个建议是不要使用when exists，而应该使用left join，使用子查询，并使用case语句中的查询。

Answer 2

所以我所做的就是。1.以下列方式删除关于专家组的案件说明。2.修改了案例语句上的SubQueries。遵循@ Sidhu推荐。

谢谢大家。