sql-server - 创建平展空值的 SQL Server (T-SQL) View

Question

我在尝试创建一个没有空值的扁平化数据 View 时遇到了很多困难。我提供了创建两个基本表的代码和我的 View 代码，这样您就可以看到我到目前为止所做的尝试。请注意，这两个表没有匹配的主键或外键列，因此 View 中的摘要仅通过加入 City 创建。我不能使用 XML，因为我的数据分析师团队都具有中级技能，无法理解它。我考虑过使用递归 CTE，但做不对。结果产生 6 行，但我想要 3 行。感谢您提供有关实现此目标的更好方法的任何想法。

CREATE TABLE A (
    OrdID int,
    Cat varchar(255),
    Qty int,
    City varchar(255),
    Ctry varchar(255)
);
INSERT INTO A (OrdID, Cat, Qty, City, Ctry)
VALUES (1, 'TV', 5,'London',  'England');
INSERT INTO A (OrdID, Cat, Qty, City, Ctry)
VALUES (2, 'Laptop', 3,'London', 'England');
INSERT INTO A (OrdID, Cat, Qty, City, Ctry)
VALUES (3, 'Laptop', 4, 'Berlin', 'Germany');

CREATE TABLE Cust (
    CustID int,
    CustType varchar(255),
    City varchar(255),
    NumItems int,
);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (1, 'New', 'London', 2);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (2, 'Returning','London', 5);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (3, 'Returning','Berlin', 2);
INSERT INTO Cust (CustID, CustType, City, NumItems)
VALUES (4, 'New','Berlin', 8);

alter view My_View
as

With CTE_FlattenNulls
as
(
Select 
S.Cat
, S.Qty
, S.City
, S.Ctry 
, case when C.CustType like 'New' then sum(C.NumItems) end as NewC
, case when C.CustType like 'Returning' then sum(C.NumItems) end as RetC
from A as S
left join Cust as C
on S.City = C.City
group by 
S.Cat
, S.Qty
, S.City
, S.Ctry
, C.CustType
)
select 
Cat
,Qty
,City
,Ctry
,NewC
,RetC
,SUM(IsNull(NewC, 0) + IsNull(RetC, 0)) as TotC
from CTE_FlattenNulls
group by
Cat
,Qty
,City
,Ctry
,NewC
,RetC

go

只需添加我想要的输出:

<表类="s-表"><头>猫数量城市国家新C重新创建总计<正文>笔记本电脑4柏林德国8210笔记本电脑3伦敦英格兰257电视5伦敦英格兰257

Answer 1

你们很亲密。请参阅代码中的注释以获取解释。

With CTE_FlattenNulls
as
(
Select S.Cat, S.Qty, S.City, S.Ctry,
    -- To do conditional summation case expression needs to be inside the SUM function
    sum( case when C.CustType like 'New' then C.NumItems else 0 end ) as NewC,
    sum( case when C.CustType like 'Returning' then C.NumItems else 0 end ) as RetC
from A as S
left join Cust as C
on S.City = C.City
group by 
S.Cat
, S.Qty
, S.City
, S.Ctry
-- then you do not need to group by this column and therefore you do not get extra rows
--, C.CustType 
)
select Cat, Qty, City, Ctry, NewC, RetC,
    -- As per your example, you would no longer need GROUP BY,
    --   therefore SUM function should be removed
    SUM(IsNull(NewC, 0) + IsNull(RetC, 0)) as TotC
from CTE_FlattenNulls
-- As per your example, you would no longer need GROUP BY
group by Cat, Qty, City, Ctry
-- ,NewC -- Definitely not needed anymore
-- ,RetC -- Definitely not needed anymore

其他一切保持不变