sql - 基于小时和时间的自定义范围字段

Question

我想根据以下两个字段（小时和时间）创建如下所示的自定义字段。有谁知道如何做到这一点是SQL？

标签： sqlsql-server

Answer 1

您不需要Hour此结果的列。Time价值观就足够了。

样本数据

create table data
(
  TimeValue time(0)
);

insert into data (TimeValue) values
('12:00:00 AM'),
('12:15:00 AM'),
('12:30:00 AM'),
('12:45:00 AM'),
( '1:00:00 AM'),
( '1:15:00 AM'),
( '1:30:00 AM'),
( '1:45:00 AM'),
( '2:00:00 AM'),
( '2:15:00 AM'),
( '2:30:00 AM'),
( '2:45:00 AM'),
( '3:00:00 AM'),
( '3:15:00 AM'),
( '3:30:00 AM'),
( '3:45:00 AM'),
( '4:00:00 AM'),
( '4:15:00 AM'),
( '4:30:00 AM');

解决方案

select 'Hour ' + convert(nvarchar(2), datepart(hour, d.TimeValue)) as [Hour],
       convert(nvarchar(11), d.TimeValue, 22) as [Time],
       'Hour ' +
       convert(nvarchar(2), case
                               when datepart(hour, d.TimeValue)-1 < 0 then 0
                               else datepart(hour, d.TimeValue)-1
                             end) + '-' +
       convert(nvarchar(2), datepart(hour, d.TimeValue)) as [Custom]
from data d;

结果

Hour    Time         Custom
------  -----------  --------
Hour 0  12:00:00 AM  Hour 0-0
Hour 0  12:15:00 AM  Hour 0-0
Hour 0  12:30:00 AM  Hour 0-0
Hour 0  12:45:00 AM  Hour 0-0
Hour 1   1:00:00 AM  Hour 0-1
Hour 1   1:15:00 AM  Hour 0-1
Hour 1   1:30:00 AM  Hour 0-1
Hour 1   1:45:00 AM  Hour 0-1
Hour 2   2:00:00 AM  Hour 1-2
Hour 2   2:15:00 AM  Hour 1-2
Hour 2   2:30:00 AM  Hour 1-2
Hour 2   2:45:00 AM  Hour 1-2
Hour 3   3:00:00 AM  Hour 2-3
Hour 3   3:15:00 AM  Hour 2-3
Hour 3   3:30:00 AM  Hour 2-3
Hour 3   3:45:00 AM  Hour 2-3
Hour 4   4:00:00 AM  Hour 3-4
Hour 4   4:15:00 AM  Hour 3-4
Hour 4   4:30:00 AM  Hour 3-4

小提琴以查看实际情况。