TSQL查询未返回预期结果 [英] TSQL query not returning expected results

问题描述

我当前的查询一直让我感到悲痛。对于任何小于或等于2810行的结果集，查询都可以工作，但它似乎在该行数处切断处理。



我错过了这个怎么回事有效吗？

  DECLARE   @ fromDate  < span class =code-keyword> DATE  
  DECLARE   @ toDate  < span class =code-keyword> DATE  
 
  SET   @ fromDate  = '  2007-09-04' 
  SET   @ toDate  = '  2016-06-28' 
 
  SELECT  DATEADD（DAY，nbr  -   1 ， @ FromDate ），nbr -1，DATEDIFF（DAY） ， @ FromDate ， @ ToDate ）
  FROM （
  SELECT  ROW_NUMBER（） OVER （ ORDER   BY  c.object_id） AS  nbr 
  FROM  sys.columns c 
）nbrs 
  WHERE  nbr  -   1 < = DATEDIFF（DAY， @ FromDate ， @ ToDate ）

我尝试过：



不知道该尝试什么。我似乎无法找到一个解决方案，除了将查询分成几个小点，在这种情况下对我来说似乎有点傻。

解决方案

sys.columns table包含数据库中每个表中每列的行。如果您的查询不会产生超过2810行，这意味着您只在数据库中定义了2810列。



有多种方法可以解决这个问题。我倾向于使用Christian Graus的解决方案：

SQL Wizardry第八部分 - 理货表 [ ^ ]

  WITH  E1（N） AS  
（
  SELECT   1   UNION   ALL   SELECT   1   UNION   ALL   SELECT   1   UNION   ALL  
  SELECT   1   UNION   ALL   SELECT   1   UNION  所有  SELECT   1   UNION  所有 
  SELECT   1   UNION  < span class =code-keyword> ALL   SELECT   1   UNION   ALL   SELECT   1   UNION   ALL   SELECT  < span class =code-digit> 1  
   -    1 * 10 ^ 1或10行 
），
 E2（N） AS  
（
  SELECT   1  
  FROM  E1 a，E1 b 
   -    1 * 10 ^ 2或100行 
），
 E4（N） AS  
（
  SELECT   1  
  FROM  E2 a，E2 b 
   -    1 * 10 ^ 4或10000行 
），
 E8（N） AS  
（
  SELECT   1  
  FROM  E4 a，E4 b 
   -    1 * 10 ^ 8或100000000行 
），
 E（N） AS  
（
  SELECT  Row_Number（） OVER  （ ORDER   BY  n）
  FROM  E8 
）
  SELECT  
 DateAdd（day，N  -   1 ， @ FromDate ），
 N  -   1 ，
 DateDiff（day ， @ FromDate ， @ ToDate ）
  FROM  
 E 
  WHERE  
 N< DateDiff（day， @ FromDate ， @ ToDate ）
; 

A current query I have has been causing me grief today. For any result set that is less than or equal to 2810 rows the query works, but it seems to cutoff processing at that rowcount.

Am I missing something in how this works?

DECLARE @fromDate DATE
DECLARE @toDate DATE

SET @fromDate = '2007-09-04'
SET @toDate = '2016-06-28'

SELECT DATEADD(DAY, nbr - 1, @FromDate), nbr -1, DATEDIFF(DAY, @FromDate, @ToDate)
FROM   (
          SELECT  ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM sys.columns c
       ) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, @FromDate, @ToDate)

What I have tried:

Not sure what to try. I can't seem to find a solution other than breaking the query into little bits which seems a little silly to me in this situation.

解决方案

The sys.columns table contains a row for each column in each table in your database. If your query won't produce more than 2810 rows, that means you only have 2810 columns defined in your database.

There are various ways you could solve this. I'd be inclined to use Christian Graus's solution:
SQL Wizardry Part Eight - Tally Tables[^]

WITH E1(N) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    -- 1*10^1 or 10 rows
),
E2(N) AS 
(
    SELECT 1 
    FROM E1 a, E1 b
     -- 1*10^2 or 100 rows
),
E4(N) AS 
(
    SELECT 1 
    FROM E2 a, E2 b
     -- 1*10^4 or 10000 rows
),
E8(N) AS 
(
    SELECT 1 
    FROM E4 a, E4 b
     -- 1*10^8 or 100000000 rows
),
E(N) AS
(
    SELECT Row_Number() OVER (ORDER BY n) 
    FROM E8
)
SELECT 
    DateAdd(day, N - 1, @FromDate), 
    N - 1, 
    DateDiff(day, @FromDate, @ToDate)
FROM
    E
WHERE 
    N < DateDiff(day, @FromDate, @ToDate)
;

这篇关于TSQL查询未返回预期结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！