时间戳

首页 > 解决方案 > 带有 CTE 变量的 SQL Server 不接受

sql - 带有 CTE 变量的 SQL Server 不接受

问题描述

使用 CTE，如果我将变量分配到外部并调用表内的变量不接受变量，但如果我直接在 CTE 查询中传递值，则返回值。

DECLARE @dates datetime = '8/5/2019 12:41:55 PM',
        @Staff nvarchar(250) = N'staff1';
WITH
CTE AS
    (SELECT DI.SchedulerID,
            DI.CustID,
            ORC.CustomerName,
            (SELECT CONVERT(char(5), DI.InTime, 108) AS [time]) AS InTime,
            DI.Identifier,
            DI.PictureUploaded,
            (SELECT CONVERT(char(5), DO.OutTime, 108) AS [time]) AS OutTime,
            CONCAT((DATEDIFF(MINUTE, DI.InTime, DO.OutTime) / 60), ':', (DATEDIFF(MINUTE, DI.InTime, DO.OutTime) % 60)) AS SpendInMinutes,
            ROW_NUMBER() OVER (PARTITION BY DI.SchedulerID,
                                            ORC.CustomerName
                               ORDER BY DI.SchedulerID DESC) AS RN
     FROM [ServiceUsers].[dbo].[DeliveryIn] DI
          INNER JOIN [Distribution].dbo.ORCustomerList ORC ON DI.CustID = ORC.CustID
          INNER JOIN [ServiceUsers].[dbo].[DeliveryOut] DO ON DI.SchedulerID = DO.SchedulerID
     WHERE (SELECT CONVERT(date, DI.InDate)) = (SELECT @dates)
       AND DI.CreatedBy = (SELECT @Staff))
SELECT *
FROM CTE
WHERE RN = 1;

如果我将值直接传递到它将返回行的位置。

标签： sqlsql-server

解决方案

评论者注意——我并不是说代码是“错误的”并且不会运行，只是它很奇怪而不是典型的编写方式

你的sql很奇怪

Where (SELECT CONVERT(date, DI.InDate))=(Select @dates) AND DI.CreatedBy=(Select @Staff)

应该

Where CONVERT(date, DI.InDate)=@dates AND DI.CreatedBy=@Staff

我也会这样写声明

DECLARE @dates datetime;
DECLARE @staff nvarchar(250);

SET @dates='2019-08-05';
SET @Staff=N'staff1';

推荐阅读