删除Null并修复对齐

Question

我使用的是SQL Server，我使用SMSS进行了查询。我不是SQL的全职用户，我正在尝试为我们的员工制作一些仪表板，使用SQL作为下面这个查询的数据收集器。

查询几乎可以工作，但我需要将所有结果放入成对的行中，而不是显示null。这是我写的查询：

SELECT convert(varchar(11), start_time, 103) AS Date, DATENAME(weekday, start_time) AS Week_Day,
CASE WHEN CAST(start_time AS time) = '05:00:00'
THEN name
END AS 'Morning',
CASE WHEN CAST(start_time AS time) ='12:59:59'
THEN name
END AS 'Afternoon',
CASE WHEN CAST(start_time AS time) = '20:59:59'
THEN name
END AS 'Evening'
FROM [somedb].[dbo].[tb_some_table]
WHERE [start_time] >= dateadd(day, 2-datepart(dw, getdate()), CONVERT(date,getdate()))
AND [start_time] < dateadd(day, 9-datepart(dw, getdate()), CONVERT(date,getdate()))

这给了我这样的结果：

Date       Week_Day Morning Afternoon Evening
24/08/2020 Monday   Fred    NULL      NULL
24/08/2020 Monday   Markus  NULL      NULL
24/08/2020 Monday   NULL    Chuck     NULL
24/08/2020 Monday   NULL    Bill      NULL
24/08/2020 Monday   NULL    NULL      John
24/08/2020 Monday   NULL    NULL      Wayne

但是，我正试图得到这个：

Date       Week_Day Morning Afternoon Evening
24/08/2020 Monday   Fred    Chuck     John
24/08/2020 Monday   Markus  Bill      Wayne

有人能帮我吗？：/

编辑：数据如下：

如果我运行一个选择，数据看起来是这样的：

start_time              end_time                name
2020-08-24 05:00:00.000 2020-08-24 12:59:59.000 Frank
2020-08-24 05:00:00.000 2020-08-24 12:59:59.000 Markus
2020-08-24 12:59:59.000 2020-08-24 20:59:59.000 Chuck
2020-08-24 12:59:59.000 2020-08-24 20:59:59.000 Bill
2020-08-24 20:59:59.000 2020-08-25 05:00:00.000 John
2020-08-24 20:59:59.000 2020-08-25 05:00:00.000 Wayne

Answer 1

我认为您想要聚合：

SELECT CONVERT(DATE, start_time) as date,
DATENAME(weekday, MIN(start_time)) AS Week_Day,
MAX(CASE WHEN CAST(start_time AS time) = '05:00:00' THEN name END) AS Morning,
MAX(CASE WHEN CAST(start_time AS time) = '12:59:59' THEN name END) AS Afternoon,
MAX(CASE WHEN CAST(start_time AS time) = '20:59:59' THEN name END) AS Evening
FROM (SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY start_time ORDER BY start_time) as seqnum
FROM [somedb].[dbo].[tb_some_table] t
WHERE start_time >= dateadd(day, 2-datepart(dw, getdate()), CONVERT(date, getdate())) AND
start_time < dateadd(day, 9-datepart(dw, getdate()), CONVERT(date, getdate()))
) t
GROUP BY CONVERT(DATE, start_time), seqnum;

请注意，您希望每个日期有多行。CCD_ 1正在确定。

注：

• 您不需要将日期转换为字符串来提取日期组件。只有当您确实希望日期具有特定格式时，才使用convert()
• 时间准确似乎很奇怪。我会期望范围。如果真的是这样，那就提出一个新的问题。它将更改查询
• 字符串和日期常量只能使用单引号。不要将它们用于列名——当(有一天(您认为使用单引号引用列时，这只会导致难以调试的错误