SQL查询必须以SELECT开头，以- can't Query开头

Question

在我的CRM系统中，我可以粘贴自己的SQL查询。添加查询表单有一个限制-它不接受不是从SELECT语句开始的查询。

我有一个从WITH语句开始的查询，但我不知道如何将它更改为从SELECT语句开始。

;WITH dRange(d) AS
(
    SELECT TOP (DATEDIFF(DAY, Convert(date, getdate()+1), GETDATE()+31)+1) 
        DATEADD(DAY, n-1, Convert(varchar(10), GETDATE()+1,120))
     FROM (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.objects) AS x

), possible(ds, de) AS
(
    SELECT DATEADD(MINUTE, 30*rn, DATEADD(HOUR, 9, dRange.d)),
        DATEADD(MINUTE, 30*rn + 60, DATEADD(HOUR, 9, dRange.d))
    FROM (SELECT TOP (720/30) rn = ROW_NUMBER() OVER
    (ORDER BY [object_id])-1 FROM sys.objects) AS x
    CROSS JOIN dRange
)
SELECT CONVERT(VARCHAR(16), p.ds, 121) AS 'Start', CONVERT(VARCHAR(16), p.de, 121) AS 'End'
FROM possible AS p 
WHERE p.de <= DATEADD(HOUR, 16, DATEADD(DAY, DATEDIFF(DAY, 0, p.de), 0)) 

也许可以以某种方式将其包装在SELECT语句中？

Answer 1

这似乎是一个晦涩难懂的问题，但您可以将查询结构化为嵌套子查询。在你的例子中，我认为这看起来像：

SELECT CONVERT(VARCHAR(16), p.ds, 121) AS 'Start', CONVERT(VARCHAR(16), p.de, 121) AS 'End'
FROM (SELECT DATEADD(MINUTE, 30*rn, DATEADD(HOUR, 9, dRange.d)),
             DATEADD(MINUTE, 30*rn + 60, DATEADD(HOUR, 9, dRange.d))
      FROM (SELECT TOP (720/30) rn = ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.objects
           ) AS x CROSS JOIN
           (SELECT TOP (DATEDIFF(DAY, Convert(date, getdate()+1), GETDATE()+31)+1) 
                   DATEADD(DAY, n-1, Convert(varchar(10), GETDATE()+1,120))
            FROM (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
                  FROM sys.objects
                 ) AS x
           ) dRange
    ) p 
WHERE p.de <= DATEADD(HOUR, 16, DATEADD(DAY, DATEDIFF(DAY, 0, p.de), 0)) 

Answer 2

多亏了戈登我找到了答案

SELECT CONVERT(VARCHAR(16), p.ds, 121) AS 'Start', CONVERT(VARCHAR(16), p.de, 121) AS 'End'
FROM (SELECT DATEADD(MINUTE, 30*rn, DATEADD(HOUR, 9, dRange.d)) as ds,
         DATEADD(MINUTE, 30*rn + 60, DATEADD(HOUR, 9, dRange.d)) as de
  FROM (SELECT TOP (720/30) rn = ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.objects
       ) AS x CROSS JOIN
       (SELECT TOP (DATEDIFF(DAY, Convert(date, getdate()+1), GETDATE()+31)+1) 
               DATEADD(DAY, n-1, Convert(varchar(10), GETDATE()+1,120)) as d
        FROM (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
              FROM sys.objects
             ) AS x
       ) dRange
) AS p
WHERE p.de <= DATEADD(HOUR, 16, DATEADD(DAY, DATEDIFF(DAY, 0, p.de), 0))