我想插入一个由此查询返回的结果:
WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY [発生時刻]) AS rn
FROM [PROC_MN].[dbo].[TBL_FINISH_STATUS]
where PO_NO='GV12762' and 発生時刻 BETWEEN '2018/03/16' AND '2018/03/18' AND [加工内容]='Bonding'
)
SELECT DATEDIFF(minute, mc.[発生時刻], mp.[発生時刻])
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1
分辨率是 : 91
我使用此查询,但不能,请帮忙!
IF OBJECT_ID('tempdb..#tempTest') IS NOT NULL
DROP TABLE #tempTest
Insert into #tempTest
WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY [発生時刻]) AS rn
FROM [PROC_MN].[dbo].[TBL_FINISH_STATUS]
where PO_NO='GV12762' and 発生時刻 BETWEEN '2018/03/16' AND '2018/03/18' AND [加工内容]='Bonding'
)
SELECT DATEDIFF(minute, mc.[発生時刻], mp.[発生時刻])
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1
编辑:这个对我有用
If(OBJECT_ID('tempdb..#temp') Is Not Null)
Begin
Drop Table #Temp
End
create table #Temp
(
OptTime int
)
;WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY [発生時刻]) AS rn
FROM [PROC_MN].[dbo].[TBL_FINISH_STATUS]
where PO_NO='GV12762' and 発生時刻 BETWEEN '2018/03/16' AND '2018/03/18' AND [加工内容]='Bonding'
)
INSERT INTO #Temp
SELECT DATEDIFF(minute, mc.[発生時刻], mp.[発生時刻])
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1
您的语法错误 - 如果要从 CTE 插入,请执行以下操作:
; WITH rows AS
(
SELECT
*, ROW_NUMBER() OVER (ORDER BY [発生時刻]) AS rn
FROM
[PROC_MN].[dbo].[TBL_FINISH_STATUS]
WHERE
PO_NO = 'GV12762'
AND 発生時刻 BETWEEN '2018/03/16' AND '2018/03/18'
AND [加工内容] = 'Bonding'
)
INSERT INTO #tempTest
SELECT (list of columns)
FROM rows
WHERE (conditions)
有关 CTE 语法及其使用方法的详细信息,请参阅官方Microsoft文档
更新:如果该目标临时表尚不存在 - 请使用以下语法:
; WITH rows AS
( ..... )
SELECT (list of columns)
INSERT INTO #tempTest
FROM rows
WHERE (conditions)
不仅语法错误,而且逻辑也不是很好。 只需使用lag()
:
INSERT INTO #tempTest
SELECT DATEDIFF(minute, LAG(fs.[発生時刻]) OVER (ORDER BY [発生時刻]), fs.[発生時刻])
FROM [PROC_MN].[dbo].[TBL_FINISH_STATUS] fs
WHERE PO_NO = 'GV12762' AND
発生時刻 >= '2018-03-16' AND
発生時刻 < '2018-03-18' AND
[加工内容] = 'Bonding';
笔记:
- 对日期使用 ISO/ANSI 标准语法。 这是一个连字符而不是斜杠。
- 不要将
BETWEEN
与日期一起使用,尤其是在它们有时间的情况下。 你可以参考Aaron Bertrand的这篇非常有用的博客文章。 - 如果使用适当的 SQL 功能,则查询将更简单。
- 如果要排除
NULL
,可以使用子查询或 CTE。