生成不存在的行

Question

当在指定条件下不存在行时，我有点困惑于如何生成行。对于格式设置表示歉意，因为我不知道如何在这样的帖子中编写表，但假设我的数据如下所示：

    TimePeriodID    CityspanSiteKey Mean_Name   Mean
    2   123 Social Environment  4
    2   123 Youth with Adults   3.666666746
    2   123 Youth with Peers    3.5
    4   123 Social Environment  2.75
    4   123 Youth with Adults   2.555555582
    4   123 Youth with Peers    3.5

我希望在每个时间段ID中包含其他几个Mean_Name值，但只包含一个Mean值为NULL，如下所示：

TimePeriodID    CityspanSiteKey Mean_Name   Mean
2   123 Social Environment  4
2   123 Youth with Adults   3.666666746
2   123 Youth with Peers    3.5
2   123 Staff Build Relationships and Support Individual Youth  NULL
2   123 Staff Positively Guide Behavior NULL
4   123 Social Environment  2.75
4   123 Youth with Adults   2.555555582
4   123 Youth with Peers    3.5
4   123 Staff Build Relationships and Support Individual Youth  NULL
4   123 Staff Positively Guide Behavior NULL
5   123 Social Environment  2.75
5   123 Youth with Adults   2.555555582
5   123 Youth with Peers    3.5
5   123 Staff Build Relationships and Support Individual Youth  NULL
5   123 Staff Positively Guide Behavior NULL
6   123 Social Environment  NULL
6   123 Youth with Adults   NULL
6   123 Youth with Peers    NULL
6   123 Staff Build Relationships and Support Individual Youth  NULL
6   123 Staff Positively Guide Behavior NULL

做这个最好的方法是什么？我认为CASEing不会有多大用处，因为这些记录并不存在。

Answer 1

你似乎想要一个cross join，然后是left join。并不是所有的值都在原始数据中，所以最好构造它们：

select ti.timeperiod, c.CityspanSiteKey, m.mean_name, t.mean
from (values (2), (4), (5), (6)
     ) ti(timeperiod) cross join
     (values (123)
     ) c(CityspanSiteKey) cross join
     (values ('Social Environment'), ('Youth with Adults'), ('Youth with Peers'), ('Staff Build Relationships and Support Individual Youth'), ('Staff Positively Guide Behavior')
     ) m(mean_name) left join
     t
     on t.timeperiod = ti.timeperiod and
        t.CityspanSiteKey = c.CityspanSiteKey and
        t.mean_name = m.mean_name;

您可以使用子查询或现有表代替values()子句。