如何按"天"和"小时"字段进行排序?

Question

我有一个字符串值作为07052018080504623

它代表MM DD YYYY HH MM SS MS

方法

MM = 07, DD = 05, YYYY=2018, HH = 08, MM=05 , SS= 04, MS = 623

现在我有一个表定义为

declare @t table (WorkRequestId varchar(100))
insert into @t values
('07052018080504623'),('07062018012756663'),('07062018020148130'),('07062018095201231'),
('07062018102203805'),('07062018103718059'),('07062018110304836'),('07062018115356135'),
('07062018120624983'),('07062018124035480'),('07062018080504623'),('07062018070504623')
select 
*   
from @t

记录应按升序排序，以便

WorkRequestId
07052018080504623
07062018095201231
07062018102203805
07062018103718059
07062018110304836
07062018115356135
07062018120624983
07062018124035480
07062018012756663
07062018020148130

截止时间为前一天晚上8点至次日下午19：59：59。 在我们的示例中，05 是前一天，而 06 是第二天。

在第二天凌晨 1 点 - 7：59：59 之间也不会发生任何交易。它从第二天早上8点再次开始，一直持续到。 下午 19：59：59。

所以当我们遇到07062018012756663, the DD=06 and HH = 01.这意味着13HRs (i.e. 1PM) of 6th.07062018020148130 where DD=06 and HH = 02 (i.e. 2PM or 14Hrs).也一样

但07062018095201231 where DD=06 and HH = 09 means 9AM of 6th.

这就是为什么

07062018095201231 comes before 07062018012756663 and 07062018020148130

订购时

到目前为止我的尝试(尚不正确(

select 
*       
,DY=SUBSTRING([WorkRequestId],3,2)
,HH = SUBSTRING([WorkRequestId],9,2)
,CurrentDY=CONVERT(varchar(2), getdate(), 103)
from @t
order by left([WorkRequestId],8) + 
cast(iif( 
SUBSTRING([WorkRequestId],3,2) = '6',--CONVERT(varchar(2), getdate(), 103), 
iif(cast(SUBSTRING([WorkRequestId],9,2) as int) between 1 and 7,
cast(SUBSTRING([WorkRequestId],9,2) as int)+12,SUBSTRING([WorkRequestId],9,2)),
cast(SUBSTRING([WorkRequestId],9,2)as varchar(4)))as varchar(20))
+right([WorkRequestId],7)

Answer 1

所以，我想你现在应该意识到将日期存储为字符串(实际上，以错误的数据类型存储任何内容(是一种不好的做法。
正确的解决方案是更改数据库结构以将该数据保存为DateTime2而不是字符串。但是，假设由于某种原因无法执行此操作，您可以通过将字符串值转换为datetime2来获得所需的结果，添加 12 小时，其中小时介于凌晨 1 点到上午 8 点之间，并按该日期排序。
我以繁琐的方式编写了我的建议，因为我想展示该过程的每个部分 - 我使用了 3 个公用表表达式，但如果可以在单个查询中完成 - 同样，这只是为了说明解决方案的每一步：

;WITH CTEDateParts AS -- break down the string to it's parts
(
SELECT  WorkRequestId, 
SUBSTRING(WorkRequestId, 5, 4) As Year,
SUBSTRING(WorkRequestId, 1, 2) As Month,
SUBSTRING(WorkRequestId, 3, 2) As Day,
SUBSTRING(WorkRequestId, 9, 2) As Hour,
SUBSTRING(WorkRequestId, 11, 2) As Minute,
SUBSTRING(WorkRequestId, 13, 2) As Second,
SUBSTRING(WorkRequestId, 15, 3) As Millisecond
FROM @t
), CTEDates AS -- create datetime values from the string parts
(
SELECT  WorkRequestId, 
CAST(Year +'-'+ Month +'-'+ Day +'T'+ 
Hour +':'+ Minute +':'+ Second +'.'+ Millisecond As DateTime2(7)) As DateValue
FROM CTEDateParts
), CTEFixedDates AS -- add 12 hours for hours between 1 and 8 a.m.
(
SELECT  WorkRequestId, 
DateValue,
CASE WHEN DATEPART(HOUR, DateValue) >= 1 AND DATEPART(HOUR, DateValue) <= 8 THEN 
DATEADD(Hour, 12, DateValue)
ELSE
DateValue
END As FixedDate 
FROM CTEDates
)
-- finally, select order by the FixedDate column
SELECT WorkRequestId
FROM CTEFixedDates
ORDER BY FixedDate

结果：

WorkRequestId
07052018080504623
07062018095201231
07062018102203805
07062018103718059
07062018110304836
07062018115356135
07062018120624983
07062018124035480
07062018012756663
07062018020148130
07062018070504623
07062018080504623

Answer 2

您可以尝试以下操作：

select 
*
from @t 
order by left (WorkRequestId, 8) + (case when SUBSTRING(WorkRequestId, 9,2) between '01' and '07' then CAST(SUBSTRING(WorkRequestId, 9,2) + 12 AS CHAR(2)) else SUBSTRING(WorkRequestId, 9,2) end) +  SUBSTRING(WorkRequestId, 11,7)