sql-server 删除空值并修复对齐

8ulbf1ek  于 2022-10-31  发布在  其他
关注(0)|答案(1)|浏览(130)

我正在使用SQL Server,并使用SMSS进行查询。我不是一个全职SQL用户,我正在尝试使用SQL作为数据收集器,使用下面的查询为我们的工作人员制作一些 Jmeter 板。
下面是我编写的一个查询,它几乎可以正常工作,但我需要将所有结果都放在成对的行中,而不是显示空值。

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

有人能帮我吗?:/
EDIT:数据如下所示:
如果我运行select,数据将如下所示:

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
w6lpcovy

w6lpcovy1#

我认为您需要聚合:

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;

请注意,您希望每个日期有多个行。ROW_NUMBER()将确定这一点。
备注:

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

相关问题