我有一個 ID 和日期表。我需要將日期轉換為日期時間，但每個時間戳在該 ID 中必須是唯一的。例如，如果這是我的表：
| ID | 日期 |
|---|---|
| 0001 | 2020-03-21 |
| 0001 | 2020-03-21 |
| 0001 | 2020-03-21 |
| 0002 | 2020-03-21 |
| 0002 | 2020-03-21 |
然后我需要我的輸出看起來像：
| ID | 約會時間 |
|---|---|
| 0001 | 2020-03-21 00:00:01 |
| 0001 | 2020-03-21 00:00:02 |
| 0001 | 2020-03-21 00:00:03 |
| 0002 | 2020-03-21 00:00:01 |
| 0002 | 2020-03-21 00:00:02 |
我很確定我們可以放心地假設沒有任何 ID 一天會有 86,400 個條目，所以我不必擔心日期意外滾動。
我在想一個游標可能是答案，但我是 SQL 的新手，并且沒有太多運氣弄清楚我將如何使用它。到目前為止，這是我嘗試過的，但并沒有讓我完全到達我需要的地方：
SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY [CID] ASC,[XDATE] ASC) AS RN
,[CID]
,[XDATE]
,dateadd(second, ((ROW_NUMBER() OVER(ORDER BY [CID] ASC,[XDATE] ASC))%86400),cast([XDATE] as datetime)) AS NEW_DATE -- 86400 sec in a day so need to mod ROW_NUMBER to prevent changing the date
FROM entryTable
This query applies the datetime addition consecutively, meaning it will occasionally happen that the time rolls over at midnight on the same day and ID as the last entry. This causes the entries to be out of order in our final database, as it sorts the data based on the timestamp, and anything showing up as post-midnight will then come before everything else that actually came first in our current database.
uj5u.com熱心網友回復：
雖然您可能沒有一個 ID 每天有超過 86,400 個條目，但聽起來您有足夠的 ID 和足夠的條目來超過 86,400個總數。為了重置問題中顯示的行號，您需要將 aPARTITION應用于視窗函式：
SELECT TOP (1000) OverallRN = ROW_NUMBER() OVER (ORDER BY CID, XDATE)
,ROW_NUMBER() OVER
(PARTITION BY CID ORDER BY [XDATE]) AS RN
,[CID]
,[XDATE]
,dateadd(second, ROW_NUMBER() OVER
(PARTITION BY CID ORDER BY [XDATE]),cast([XDATE] as datetime)) AS NEW_DATE
FROM dbo.entryTable;
- 示例db<>fiddle
如果對于任何給定的 ID/日期組合，您仍然超過 86,400 個條目，您需要定義如何處理 86,401 ，因為mod (%)如果您想保持原始序列，肯定不會這樣做。也許您需要比一秒鐘更細化，例如添加ROW_NUMBER() * 100 milliseconds. 雖然如果這些是表中僅有的兩列，我不知道你怎么會知道任何事情都是亂序的。
uj5u.com熱心網友回復：
用這個
SELECT ID ,Date,
concat(Date,' ',DATEADD(second,
CAST(43200000 * (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 0.5) as int),
CAST('00:00:00' AS Time))) AS datetime
from entrytable
或更適合您的查詢
SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY [CID] ASC,[XDATE] ASC) AS RN
,[CID]
,[XDATE]
, concat(Date,' ',DATEADD(second,
CAST(43200000 * (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 0.5) as int),
CAST('00:00:00' AS Time))) AS datetime
FROM entryTable
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/447524.html
標籤：sql sql-server date datetime
上一篇：如何在mysql中跳過幾天？