按选择大小写或子选择进行分组

Question

我有一个产品列表，我想按它们的组件对它们进行分组。我所命名的SIAC是每个最终产品的主要组件，有时客户会购买这个组件而不是产品，所以我需要将最终组件(产品的SIAC(和SIAC作为一个产品本身来计算。该选择返回了我为未决订单提供服务所需的所有SIAC。

select      isnull((select case when l.codart like 'SIAC%' 
then l.codart
else (select e.codartc from escandallo e where e.codartp = l.codart and e.codartc like 'SIAC%') 
end as SIAC), 'NO SIAC') as 'REF',
sum(l.unidades - l.unianulada - l.uniservida) AS PENDING_UNITS
from        LINEPEDI l 
where       ((l.unidades - l.unianulada - l.uniservida) * l.precio) > 0
and isnull((select case when l.codart like 'SIAC%' 
then l.codart 
else (select e.codartc from escandallo e where e.codartp = l.codart and e.codartc like 'SIAC%') 
end as SIAC), 'NO SIAC') like 'SIAC%'
group by    l.codart
order by    l.codart asc

但我不知道我怎么能用"；REF"；

按最终产品分组(l.codart(返回此

REF         PENDING_UNITS
SIACZM016   300
SIACZM017   1200
SIACZM017   500
SIACZM017   900
SIACZM018   400

因此，有3个最终产品具有相同的主要成分(SIACZM017(。

我如何根据这句话进行分组？

isnull((select case when l.codart like 'SIAC%' 
then l.codart
else (select e.codartc from escandallo e where e.codartp = l.codart and e.codartc like 'SIAC%') 
end as SIAC), 'NO SIAC')

非常感谢

Answer 1

只需使用横向连接，不仅可以按它分组，还可以避免重复。

select R.Ref
, sum(l.unidades - l.unianulada - l.uniservida) AS PENDING_UNITS
from LINEPEDI l 
cross apply (
select isnull((
select case when l.codart like 'SIAC%' 
then l.codart 
else (select e.codartc from escandallo e where e.codartp = l.codart and e.codartc like 'SIAC%') 
end as SIAC), 'NO SIAC') Ref
) R
where ((l.unidades - l.unianulada - l.uniservida) * l.precio) > 0
and R.Ref like 'SIAC%'
group by R.Ref
order by R.Ref asc;