SQL:如何使用仅代表一周中某些日子的数据显示一周中的全部7天

Question

我有一个表，它存储一周中几天的数据。我想为一周中的每一天返回一行，即使我的表中没有这一天的任何行。这是我当前的sql select语句。

SELECT StoreID,
   CASE 
       WHEN S.[DayOfWeek] = 1 THEN 'Sunday' 
       WHEN S.[DayOfWeek] = 2 THEN 'Monday'
       WHEN S.[DayOfWeek] = 3 THEN 'Tuesday'
       WHEN S.[DayOfWeek] = 4 THEN 'Wednesday'
       WHEN S.[DayOfWeek] = 5 THEN 'Thursday'
       WHEN S.[DayOfWeek] = 6 THEN 'Friday'
       WHEN S.[DayOfWeek] = 7 THEN 'Saturday'
       ELSE 'BAD' 
   END AS [DayOfWeek], 
       isOpen
  FROM MyTable S
 WHERE StoreID = @I_StoreID
 ORDER BY S.[DayOfWeek]  

现在它只返回一个星期一和星期二的记录，因为这是表中存在的全部内容，但我希望它也返回其他行，即使目前没有记录。谢谢

编辑：

这是我的。。。

  StoreID | DayOfWeek | isOpen
      22     Sunday      0
      22     Monday      1
      29     Sunday      0

以下是我希望得到的。。。

  StoreID | DayOfWeek | isOpen
      22     Sunday      0
      22     Monday      1
      22     Tuesday     NULL
       ....
      22     Saturday    NULL
      29     Sunday      1
      29     Monday      NULL 
      29     Tuesday     NULL
       ....
      29     Saturday    NULL

Answer 1

您可以使用以下解决方案：

SELECT
    a.StoreID,
    a.weekdayname,
    b.isOpen
FROM
(
    SELECT *
    FROM
    (
        SELECT DISTINCT StoreID
        FROM MyTable
    ) aa
    CROSS JOIN
    (
        SELECT 1 AS weekdaynum, 'Sunday' AS weekdayname UNION ALL
        SELECT 2, 'Monday'                UNION ALL
        SELECT 3, 'Tuesday'               UNION ALL
        SELECT 4, 'Wednesday'             UNION ALL
        SELECT 5, 'Thursday'              UNION ALL
        SELECT 6, 'Friday'                UNION ALL
        SELECT 7, 'Saturday'
    ) bb
) a
LEFT JOIN
    MyTable b ON a.StoreID = b.StoreID AND 
                 a.weekdaynum = b.[DayOfWeek]
WHERE
    a.StoreID = @I_StoreID
ORDER BY
    a.StoreID, a.weekdaynum

在这里，我们手动选择所有工作日名称，并将它们与每个不同的StoreID一起CROSS JOIN。然后，我们将该选择的结果封装在FROM子句中，并在StoreID和工作日编号匹配的条件下，将LEFT JOIN执行回主表。如果不是，则isOpen字段将为NULL，但StoreID和相应的工作日仍将显示。

---

SQLFiddle演示

Answer 2

如果isOpen为1表示打开，为0表示关闭，则可以进行

select StoreID,
   max(case when s.[DayOfWeek] = 1 then isOpen else 0 end) as Sunday,
   max(case when s.[DayOfWeek] = 2 then isOpen else 0 end) as Monday,
   max(case when s.[DayOfWeek] = 3 then isOpen else 0 end) as Tuesday,
   max(case when s.[DayOfWeek] = 4 then isOpen else 0 end) as Wednesday,
   max(case when s.[DayOfWeek] = 5 then isOpen else 0 end) as Thursday,
   max(case when s.[DayOfWeek] = 6 then isOpen else 0 end) as Friday,
   max(case when s.[DayOfWeek] = 7 then isOpen else 0 end) as Saturday
from MyTable S
where StoreID = @I_StoreID
group by StoreID

这将更改查询的格式，但如果商店当天开门，则应为一周中的每一天提供1列。

Answer 3

编辑：在看到您的更新响应之前，我已经更改了这一点。考虑到你想要看到的数据，另一个答案应该会更好

您是否有一个独立于MyTable的具有DayOfWeek的表？如果整个表中没有一条DayOfWeek=1的记录，那么在开始使用MyTable之前，您必须引入一个单独的DayOfWeek表来列出这些值。

以下是一个假设的DayOfWeek表和将产生的SQL：

DayOfWeek表
DayOfWeek:名称
1:星期日
2：星期一
3：星期二
4:星期三
5:星期四
6:星期五
7：周六

查询

SELECT D.Name,
S.isOpen,
COUNT(S.StoreID) as StoreID_Count
FROM DayOfWeek D
LEFT OUTER JOIN MyTable S ON S.DayOfWeek = D.DayOfWeek
WHERE S.StoreID = @I_StoreID
GROUP BY D.[DayOfWeek], S.isOpen
ORDER BY D.[DayOfWeek]

我相信这至少会让你接近。