使用 CTE 的 TSQL 语句的执行顺序 [英] Order of execution for TSQL statement with CTE

问题描述

我正在为通用表的表运行报告.因此，ParameterValue"字段中的值将包含来自许多不同类型的数据.我想要做的是仅在ParameterName"列等于Historian Timestamp"时才执行转换.

I'm working on running a report for a table that generic table. So the values in the "ParameterValue" field will contain data from many different types. What I am trying to do is to only perform the conversion if the "ParameterName" column is equal 'Historian Timestamp'.

这是我正在运行的查询...

This is the query that I am running...

WITH LogbookSourceObjects AS (
    SELECT CAST(obj.NAME AS INT) as LogbookId, ObjectId 
    FROM PISourceObject obj
    JOIN PISource s ON s.SourceID = obj.SourceId
    WHERE s.Name ='DEDR' AND ISNUMERIC(obj.NAME) = 1
), 
Comments AS (
    SELECT lso.LogbookId, 
           c.CommentId, 
           c.CommentTypeId, 
           cd.Comment, 
           cd.CommentDetailTime, 
           u.FirstName, 
           u.LastName,
           cp.ParameterValue, 
           p.Name, 
           CONVERT(DATETIMEOFFSET, cp.ParameterValue) AS HistorianTimestamp
     FROM LogbookSourceObjects lso 
     JOIN PIComment c ON c.ObjectId = lso.ObjectId
     JOIN PICommentDetail cd ON cd.CommentId = c.CommentId 
     JOIN PICommentType ct ON ct.CommentTypeId = c.CommentTypeId
     JOIN PICommentParameter cp on cp.CommentId = c.CommentId 
     JOIN PIParameter p on cp.ParameterId = p.ParameterId
     JOIN PIUser u on u.UserId = cd.UserId 
     WHERE p.Name ='Historian Timestamp')
SELECT * FROM COMMENTS

返回以下数据

╔═══════════╦═══════════╦═══════════════╦══════════════════╦═══════════════════╦═══════════╦══════════╦═══════════════════════════╦═════════════════════╦════════════════════════════════════╗
║ LogbookId ║ CommentId ║ CommentTypeId ║     Comment      ║ CommentDetailTime ║ FirstName ║ LastName ║      ParameterValue       ║        Name         ║         HistorianTimestamp         ║
╠═══════════╬═══════════╬═══════════════╬══════════════════╬═══════════════════╬═══════════╬══════════╬═══════════════════════════╬═════════════════════╬════════════════════════════════════╣
║         1 ║         2 ║             1 ║ I entered 1      ║ 53:39.8           ║ Jason     ║ Turan    ║ 2016-11-29T12:47:14       ║ Historian Timestamp ║ 2016-11-29 12:47:14.0000000 +00:00 ║
║         1 ║        54 ║             1 ║ Note on tablet.  ║ 42:01.8           ║ Jason     ║ Turan    ║ 2016-12-05T13:36:34       ║ Historian Timestamp ║ 2016-12-05 13:36:34.0000000 +00:00 ║
║         1 ║        55 ║             1 ║ testnotes        ║ 47:37.7           ║ Desiree   ║ Teter    ║ 2016-12-07T15:13:29       ║ Historian Timestamp ║ 2016-12-07 15:13:29.0000000 +00:00 ║
║         4 ║        56 ║             1 ║ notes            ║ 09:16.4           ║ Desiree   ║ Teter    ║ 2016-12-08T14:00:56       ║ Historian Timestamp ║ 2016-12-08 14:00:56.0000000 +00:00 ║
║         4 ║        56 ║             1 ║ notes 2          ║ 09:39.5           ║ Desiree   ║ Teter    ║ 2016-12-08T14:00:56       ║ Historian Timestamp ║ 2016-12-08 14:00:56.0000000 +00:00 ║
║         4 ║        57 ║             1 ║ ?                ║ 36:19.2           ║ Desiree   ║ Teter    ║ 2016-12-08T14:00:56       ║ Historian Timestamp ║ 2016-12-08 14:00:56.0000000 +00:00 ║
║         4 ║        59 ║             1 ║ testnotes sdfsdf ║ 29:42.1           ║ Desiree   ║ Teter    ║ 2016-12-08T14:00:56-06:00 ║ Historian Timestamp ║ 2016-12-08 14:00:56.0000000 -06:00 ║
╚═══════════╩═══════════╩═══════════════╩══════════════════╩═══════════════════╩═══════════╩══════════╩═══════════════════════════╩═════════════════════╩════════════════════════════════════╝

但是，当我在 CTE 列HistorianTimestamp"上添加过滤器时.我收到以下错误.

However when I add a filter on the CTE Column "HistorianTimestamp". I get the following error.

消息 241，级别 16，状态 1，第 1 行从字符串转换日期和/或时间时转换失败.

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

WITH LogbookSourceObjects AS (
    SELECT CAST(obj.NAME AS INT) as LogbookId, ObjectId 
    FROM PISourceObject obj
    JOIN PISource s ON s.SourceID = obj.SourceId
    WHERE s.Name ='DEDR' AND ISNUMERIC(obj.NAME) = 1
), 
Comments AS (
    SELECT lso.LogbookId, 
           c.CommentId, 
           c.CommentTypeId, 
           cd.Comment, 
           cd.CommentDetailTime, 
           u.FirstName, 
           u.LastName,
           cp.ParameterValue, 
           p.Name, 
           CONVERT(DATETIMEOFFSET, cp.ParameterValue) AS HistorianTimestamp
     FROM LogbookSourceObjects lso 
     JOIN PIComment c ON c.ObjectId = lso.ObjectId
     JOIN PICommentDetail cd ON cd.CommentId = c.CommentId 
     JOIN PICommentType ct ON ct.CommentTypeId = c.CommentTypeId
     JOIN PICommentParameter cp on cp.CommentId = c.CommentId 
     JOIN PIParameter p on cp.ParameterId = p.ParameterId
     JOIN PIUser u on u.UserId = cd.UserId 
     WHERE p.Name ='Historian Timestamp')
SELECT * FROM COMMENTS
WHERE HistorianTimestamp > CONVERT(DATETIMEOFFSET, '2016-11-29T00:00:00-06:00') AND HistorianTimestamp <  CONVERT(DATETIMEOFFSET, '2016-11-30T00:00:00-06:00')

我认为这可能是由于执行引擎决定在 where 子句之前执行 select 语句.不过这不是错吗?执行引擎不应该遵守语句的执行顺序吗?IE 在选择之前应用 where 子句.如果不是，我该如何重写该语句?

I'm thinking it's probably due to the execution engine deciding to do the select statement before the where clause. Isn't that wrong though? Shouldn't the order of execution of statements be respected by the execution engine? IE the where clause is applied before selects. If not how can I rewrite the statement?

推荐答案

首先，只要你有:

SELECT * FROM <someCTE>

恕我直言，这意味着您的 CTE 过多.

That means you have too many CTEs IMHO.

您可以像这样简化您的查询:

You could simplify your query like this:

WITH LogbookSourceObjects AS (
    SELECT CAST(obj.NAME AS INT) as LogbookId, ObjectId 
    FROM PISourceObject obj
    JOIN PISource s ON s.SourceID = obj.SourceId
    WHERE s.Name ='DEDR' AND ISNUMERIC(obj.NAME) = 1
)
SELECT lso.LogbookId, 
       c.CommentId, 
       c.CommentTypeId, 
       cd.Comment, 
       cd.CommentDetailTime, 
       u.FirstName, 
       u.LastName,
       cp.ParameterValue, 
       p.Name, 
       CONVERT(DATETIMEOFFSET, cp.ParameterValue) AS HistorianTimestamp
FROM LogbookSourceObjects lso 
JOIN PIComment c ON c.ObjectId = lso.ObjectId
JOIN PICommentDetail cd ON cd.CommentId = c.CommentId 
JOIN PICommentType ct ON ct.CommentTypeId = c.CommentTypeId
JOIN PICommentParameter cp on cp.CommentId = c.CommentId 
JOIN PIParameter p on cp.ParameterId = p.ParameterId
JOIN PIUser u on u.UserId = cd.UserId 
WHERE p.Name ='Historian Timestamp'
AND CONVERT(DATETIMEOFFSET, cp.ParameterValue) > CONVERT(DATETIMEOFFSET, '2016-11-29T00:00:00-06:00') 
AND CONVERT(DATETIMEOFFSET, cp.ParameterValue) < CONVERT(DATETIMEOFFSET, '2016-11-30T00:00:00-06:00');

这几乎肯定会失败，但会使故障排除更容易一些.然后你可以尝试(从我的头顶):

This will almost certainly still fail but will make troubleshooting a little easier. You could then try (off the top of my head):

AND ISDATE(CONVERT(DATETIMEOFFSET, cp.ParameterValue)) = 1

或许……

AND TRY_CONVERT(DATETIMEOFFSET, cp.ParameterValue) > CONVERT(DATETIMEOFFSET, '2016-11-29T00:00:00-06:00') 
AND TRY_CONVERT(DATETIMEOFFSET, cp.ParameterValue) < CONVERT(DATETIMEOFFSET, '2016-11-30T00:00:00-06:00');

只是一些思考的食物.

这篇关于使用 CTE 的 TSQL 语句的执行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！