SQL：我如何使用仅表示本周几天的数据显示一周中的全部7天？

问题描述

我有一张表格，用于存储一周中的几天的数据。即使在我的表中没有任何一周中的某一行，我也想为每周的每一天返回一行。

  SELECT StoreID，
 CASE 
当[[DayOfWeek] = 1 THEN'Sunday'
 S. [DayOfWeek] = 2 THEN'Monday'
 S. [DayOfWeek] = 3 THEN'Tuesday'
 S. [DayOfWeek] = 4 THEN '星期三'
当S. [DayOfWeek] = 5 THEN'Thursday'
当S. [DayOfWeek] = 6 THEN'Friday'
当S. [DayOfWeek] = 7 THEN'星期六'
 ELSE'BAD'
 END AS [DayOfWeek]，
 isOpen 
 FROM MyTable S 
 WHERE StoreID = @I_StoreID 
 ORDER BY S. [DayOfWeek ] 
  

现在它只返回星期一和星期二的记录，因为这就是表格中存在的所有内容，但我希望它也能返回其他行，即使当前没有记录。

编辑：

这是我的...

  StoreID | DayOfWeek | isOpen 
 
 22星期天0 
 22星期一1 
 29星期日0 
  

以下是我希望获得的信息...

  StoreID | DayOfWeek | isOpen 
 
 22星期日0 
 22星期一1 
 22星期二NULL 
 .... 
 22星期六NULL 
 
 29星期天1 
 29星期一NULL 
 29星期二NULL 
 .... 
 29星期六NULL 
  

解决方案

您可以使用此解决方案：

  SELECT 
 a.StoreID，
 a.weekdayname，
 b.isOpen 
 FROM 
（
 SELECT * 
 FROM 
（ 
 SELECT DISTINCT StoreID 
 FROM MyTable $ b $ a）
 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 A LL 
 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 
  

在这里，我们手动选择所有周日名称和 CROSS JOIN 它们与每个不同的 StoreID 。然后，我们将该选择的结果包装在 FROM 子句中，并执行 LEFT JOIN 到主表条件是 StoreID 以及星期几号码匹配。如果不是，那么 isOpen 字段将是 NULL ，但是 StoreID 和相应的工作日仍会显示。

---

I have a table that stores data on days of the week. I'm wanting to return a row for each day of the week even if there isn't any rows for that day of the week in my table. Here's my current sql select statement.

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]

Right now it returns just a Monday and Tuesday record because that's all that exists int he table, but I want it to also return the other rows even though there are no records currently for them. Thanks!

Edit:

Here is what I have ...

  StoreID | DayOfWeek | isOpen

      22     Sunday      0
      22     Monday      1
      29     Sunday      0

Here is what I'm hoping to get...

  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

解决方案

You can use this solution:

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

Here, we manually select all weekday names and CROSS JOIN them with each distinct StoreID. We then wrap the result of that selection in the FROM clause and perform a LEFT JOIN back onto the main table on the condition that StoreID as well as the weekday number match. If not, then the isOpen field will be NULL, but the StoreID and corresponding weekday will still display.

---

SQLFiddle Demo

这篇关于SQL：我如何使用仅表示本周几天的数据显示一周中的全部7天？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！