sql-server - 当 SQL 中的组合没有数据时创建空行

Question

大多数人可能会问过这个问题，我也尝试过，但找不到解决方案。我有一个包含 15 个非度量属性/列和 15 个度量列的表。对于非度量列 - 如果任何组合不存在，我想创建空值。我正在使用 SAS，它可以有简单的 PROC SQL 语句。假设我有这样的数据： 在此处输入图像描述

Name | Location | Holiday | Time | DishCnt | TastedGood
Bill    1st Fl.    Easter    AM       3          Yes
Bill    1st Fl.    Easter    PM       3          Yes
Bill    1st Fl.    Kawanza   AM       2          Yes
Bill    2st Fl.    Kawanza   AM       2          Yes
Sameer  1st Fl.    Easter    AM       1          No
Sameer  1st Fl.    Easter    PM       2          Yes
Sameer  1st Fl.    Kawanza   AM       1          Yes
Peter   1st Fl.    Easter    AM       1          No
Peter   1st Fl.    Kawanza   AM       2          No

我想检查每个非测量行的组合，如果没有，那么我想创建一个新行并将其设置为 null 或 0。 在此处输入图像描述 我想要的示例数据如下所示：

Name | Location | Holiday | Time | DishCnt | TastedGood
Bill    1st Fl.    Easter    AM       3          Yes
Bill    1st Fl.    Easter    PM       3          Yes
Bill    1st Fl.    Kawanza   AM       2          Yes
Bill    2st Fl.    Kawanza   AM       2          Yes
Sameer  1st Fl.    Easter    AM       1          No
Sameer  1st Fl.    Easter    PM       2          Yes
Sameer  1st Fl.    Kawanza   AM       1          Yes
Sameer  2st Fl.    Kawanza   AM      NULL        NULL
Peter   1st Fl.    Easter    AM       1          No
Peter   1st Fl.    Kawanza   AM       2          No
Peter   1st Fl.    Easter    PM      NULL        NULL
Peter   2st Fl.    Kawanza   AM      NULL        NULL

我所做的代码是我尝试交叉加入同一个表并加入公共列。像这样的东西

SELECT * 
FROM Holidays H
CROSS JOIN Holidays T
LEFT JOIN Holidays R ON H.HoludayKey = R.HolidayKey AND T.TimeKey=R.TimeKey

提前感谢您的帮助。

谢谢！

标签： sql-serversasproc-sql

Answer 1

考虑名称、位置、假日和时间的所有可能值的交叉连接数据集。LEFT JOIN然后使用实际数据集运行此查询。任何不匹配的行都将被填充NULL或丢失。

proc sql;
   CREATE TABLE combns AS
   SELECT n.Name
        , m.Location
        , m.Holiday
        , m.Time
   FROM 
       (SELECT DISTINCT Name
        FROM Holidays) n
   CROSS JOIN
       (SELECT DISTINCT Location, Holiday, Time
        FROM Holidays) m;
   
   CREATE TABLE final_table AS
   SELECT c.Name
        , c.Location
        , c.Holiday
        , c.Time
        , h.DishCnt
        , h.TastedGood
   FROM combns c
   LEFT JOIN Holidays h
    ON c.Name = h.Name 
    AND c.Location = h.Location
    AND c.Holiday = h.Holiday
    AND c.Time = h.Time;
quit;

---

输入 （OP 发布的数据）

data Holidays;
    infile datalines delimiter=',' DSD; 
    length Name $8 Location $8 Holiday $8 Time $2 DishCnt TastedGood $3;
    
    input Name Location Holiday Time DishCnt TastedGood;
    datalines;
Bill,1st Fl.,Easter,AM,3,Yes
Bill,1st Fl.,Easter,PM,3,Yes
Bill,1st Fl.,Kawanza,AM,2,Yes
Bill,2st Fl.,Kawanza,AM,2,Yes
Sameer,1st Fl.,Easter,AM,1,No
Sameer,1st Fl.,Easter,PM,2,Yes
Sameer,1st Fl.,Kawanza,AM,1,Yes
Peter,1st Fl.,Easter,AM,1,No
Peter,1st Fl.,Kawanza,AM,2,No
;

输出 （上面运行后proc sql）