我有一张表,用于存储一周中各天的数据。我想在一周中的每一天都返回一行,即使表中一周中的这一天都没有任何行。这是我当前的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
最佳答案
您可以使用以下解决方案:
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
在这里,我们手动选择所有工作日名称,并使用每个不同的
CROSS JOIN
对其进行StoreID
编码。然后,将选择的结果包装在FROM
子句中,并在LEFT JOIN
和工作日数字匹配的情况下将StoreID
返回到主表。如果不是,那么isOpen
字段将为NULL
,但是仍然会显示StoreID
和相应的工作日。SQLFiddle Demo
关于sql - SQL:如何显示仅代表一周中某几天的数据显示一周中的整整7天?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/11714390/