避免在中使用group子句或聚合函数

Question

我在查询中使用聚合函数/group子句，但没有获得所要的输出。我的table有许多列，但只是为了测试目的，我在下面的示例中显示-

Supplier Table -

SpID    SupName   
1       Test
2       Test2
3       Test3   

Stock Table (has more columns) -

SID      ReplaceID   SupID     Qty      PName   PSize
1          11           1       2         P1x    5-6
2                       2       5         P2     11Y
11                      1       4         P1     6Y
11                      1       7         P1     6Y
12                      3       10        P12    1-2

我想要的输出.当Supplier ID是1时，我需要加入SID and ReplaceID Qty并显示ID值没有ReplaceID。

我要低于产量-

ID  OldID  SupName  PNAME    PSize  Qty         
11    1      Test     P1      6Y    13

但产量越来越低-

ID  OldID   SupName PName   PSize   Qty
11  NULL      Test   P1      6Y      11
11  1         Test   P1x     5-6     2

我的疑问是-

; with
cte as
(
    -- Replace ID
    select ID = ReplaceID, OldID=SID, PName, PSize, SupID, TotalQty = SUM(QTY)
    from Stock ST
    where exists
        (
            select *
            from Stock s
            where s.SID = ST.ReplaceID
        )
    group by ReplaceID, SID, PName, PSize, SupID

    union all

    -- without Replace ID
    select ID = SID, NULL AS OldID, PName, PSize, SupID, TotalQty = SUM(QTY)
    from Stock ST
    where not exists
        (
            select *
            from Stock s
            where s.SID = ST.ReplaceID
        )
    group by SID, PName, PSize, SupID
)
select  c.ID, c.OldID, SP.SupName, PName, PSize, Qty = SUM(TotalQty)
from    cte c
        inner join Supplier SP on c.SupID = SP.SpID
where   c.SupID = 1
group by c.ID, c.OldID, SupName, PName, PSize
order by c.ID

有人能帮我实现这个目标吗？如果可能的话，我不喜欢使用聚合函数。

谢谢

Answer 1

这产生了您所要求的结果，至少消除了一些复杂性：

declare @suppliers table (SpID int not null, SupName varchar(10) not null)
insert into @suppliers(SpID,SupName) values
(1,'Test '),
(2,'Test2'),
(3,'Test3')

declare @stock table (SID int not null, ReplaceID int null, SupID int not null,
Qty int not null, PName varchar(10) not null, PSize varchar(10) not null)
insert into @stock(SID,ReplaceID,SupID,Qty,PName,PSize) values
(1 ,11  ,1,2 ,'P1x','5-6'),
(2 ,null,2,5 ,'P2 ','11Y'),
(11,null,1,4 ,'P1 ','6Y '),
(11,null,1,7 ,'P1 ','6Y '),
(12,null,3,10,'P12','1-2')

declare @SupID int
set @SupID = 1

select
    COALESCE(st2.SID,st1.SID) as SID,
    MAX(CASE WHEN st1.ReplaceID is not null then st1.SID END) as OldID,
    sp.SupName,
    COALESCE(st2.PName,st1.PName) as PName,
    COALESCE(st2.PSize,st1.PSize) as PSize,
    SUM(Qty) as Qty
from
    @suppliers sp
        inner join
    @stock st1
        on
            sp.SpID = st1.SupID
        left join
    (select SID,SupID,MAX(PName) as PName,MAX(PSize) as PSize from @stock 
     GROUP By SID,SupID) st2
        on
            sp.SpID = st2.SupID and
            st2.SID = st1.ReplaceID
where
    sp.SPID = @SupID
group by
    COALESCE(st2.SID,st1.SID),
    sp.SupName,
    COALESCE(st2.PName,st1.PName),
    COALESCE(st2.PSize,st1.PSize)

结果：

SID         OldID       SupName    PName      PSize      Qty
----------- ----------- ---------- ---------- ---------- -----------
11          1           Test       P1         6Y         13

正如我所指出的，我们需要使用一些聚合来获取Qty。这是基于这样的假设，即PName和PSize对于每个SID值都是唯一的(这意味着标准化程度很差)。我还假设ReplacementID来自与原始供应商相同的供应商。

在SUM(Qty)子查询中(因为我们已经在那里聚合了)，使Qty位于外部st1.Qty + COALESCE(st2.Qty,0)中，删除OldID表达式周围的MAX，然后可以消除外部GROUP BY，这是上述方法的另一种形式。然而，上面的这些是我第一次读到的“清洁工”。

Answer 2

这有用吗？

    Create Table #Supplier(SpID int,SupName Varchar(100))
    Create Table #Stock([SID] int,ReplaceID int, SupID int,Qty int,PName varchar(100), PSize varchar(100))

    Insert Into #Supplier
    SELECT 1,'Test' Union All
    SELECT 2,'Test2' Union All
    SELECT 3,'Test3'   


    Insert Into #Stock
    SELECT 1,11  ,1,2,'P1x','5-6' Union All
    SELECT 2,NULL,2,5,'P2','11Y' Union All
    SELECT 11,NULL,1,4,'P1','6Y' Union All
    SELECT 11,NULL,1,7,'P1','6Y' Union All
    SELECT 12,NULL,3,10,'P12','1-2'

    ;with cte1
    As

    (
    Select ISNULL(st.ReplaceID,SpID) As ID, SpID As OldID, SupName,
     ISNULL(strp.PName,st.PName) PName,ISNULL(strp.PSize,st.PSize) PSize,SUM(ISNULL(strp.Qty,0)) as Qty
    from #Supplier sp
    LEFT JOIN #Stock st on sp.SpID=st.SID
    LEFT JOIN #Stock strp on st.ReplaceID=strp.SID
    Group by ISNULL(st.ReplaceID,SpID) , SpID , SupName,ISNULL(strp.PName,st.PName),ISNULL(strp.PSize,st.PSize)
    )
    ,cte2
    As
    (
    Select ISNULL(st.ReplaceID,SpID) As ID, SpID As OldID, SupName,st.Qty  
    from #Supplier sp
    LEFT JOIN #Stock st on sp.SpID=st.SID
    Where ReplaceId is Not NULL And ReplaceID!=''
    )

    Select c1.id,c1.OldID, c1.SupName, c1.PName,c1.PSize,c1.Qty+c2.Qty As Qty
    from cte1 c1
    Left JOIN cte2 c2 on c1.Id=c2.id and c1.OldID=c2.OldID
    order by OldID


    Drop Table #Supplier
    Drop Table #Stock