sql - INSERT - 为插入的每一行自动增加日期时间的 SELECT
问题描述
我有一张桌子,上面有 3 个键,AUDIT_OPRID
( varchar
)、AUDIT_STAMP
( datetime
)、AUDIT_ACTN
( varchar
)。
我正在从 select 语句中插入行,使用该GETDATE()
函数获取当前日期时间。AUDIT_OPRID
和的值AUDIT_ACTN
是相同的,所以我唯一可以使唯一的键列是日期时间。我希望有一种方法可以为插入的每一行自动增加 1 秒的日期时间,这样我就不会遇到重复的键问题。
我找到了一个类似情况的 MySQL 版本的线程 - SQL INSERT...SELECT with auto-increment DATETIME 但是在以下 SQL 中使用它时出现语法错误:
DECLARE @value DATETIME
SET @value = CURRENT_TIMESTAMP
INSERT INTO PS_AUDIT_PSROLEUSR (AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN, ROLEUSER, ROLENAME, DYNAMIC_SW)
SELECT
'TESTUSER', @value = @value + INTERVAL 1 SECOND, 'D', A.OPRID, D.ROLENAME, 'N'
FROM
PSOPRDEFN A
INNER JOIN
PS_AD_X_WALK B ON B.OPRID = A.OPRID
INNER JOIN
PS_JOB C ON C.EMPLID = B.GH_AD_EMPLID
WHERE
B.GH_AD_EMPLID <> ''
AND C.ACTION = 'TER'
AND A.ACCTLOCK = 0
理想的结果如下:
AUDIT_OPRID AUDIT_STAMP AUDIT_ACTN ROLEUSER ROLENAME DYNAMIC_SW
TESTUSER 2018-11-21 07:02:08.563 D USER123452 GHS_PO_RECEIVER N
TESTUSER 2018-11-21 07:02:09.563 D USER123452 GHS_STOREROOM N
TESTUSER 2018-11-21 07:02:10.563 D USER123452 GH_EPRO N
解决方案
您可以使用 row_number 并将该值作为秒添加到您的时间戳值中,如下所示
DECLARE @value DATETIME
SET @value = CURRENT_TIMESTAMP
INSERT INTO PS_AUDIT_PSROLEUSR (AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN, ROLEUSER, ROLENAME, DYNAMIC_SW)
SELECT
'TESTUSER', DATEADD(SECOND, ROW_NUMBER() OVER (ORDER BY A.OPRID), @value),
'D', A.OPRID, D.ROLENAME, 'N'
FROM
PSOPRDEFN A
INNER JOIN
PS_AD_X_WALK B ON B.OPRID = A.OPRID
INNER JOIN
PS_JOB C ON C.EMPLID = B.GH_AD_EMPLID
WHERE
B.GH_AD_EMPLID <> ''
AND C.ACTION = 'TER'
AND A.ACCTLOCK = 0
推荐阅读
- asp.net - Differences between audience, issuer, and client terms in JWT, OAuth and OIDC
- vue.js - How can I modify a Vuetify sass variable for a specific instance of a component?
- java - 从 java.util.ArrayList 到 java 中的字典
- laravel - js file not loading giving ERR_ABORTED 404
- android - Is there a way to stop accessibility services to interact with my android application?
- javascript - Javascript getting a function to execute based on and/or
- python - 等到线程池进程完成后再提交下一个进程
- amazon-web-services - AWS pre-signed URL returns Signature mismatch on new bucket
- java - 关于在java中打印i++的while循环问题
- angular - 导入 MobxAngularModule 导致 Ionic 应用程序编译失败