时间戳

首页 > 解决方案 > 在 DB2 SQL 中，如何使用 FETCH FIRST (n) ROWS ONLY..返回可变数量的行？

db2 - 在 DB2 SQL 中，如何使用 FETCH FIRST (n) ROWS ONLY..返回可变数量的行？

问题描述

如何FETCH FIRST (n) ROWS ONLY在 DB2 SQL 中返回可变 (n) 行数？这适用于 DB2/400 v7r3。

的文档FETCH确实明确指出它无法完成，但我无法想象另一种方式来做到这一点......

...fetch-row-count 不得包含标量全查询、列引用、表引用、用户定义函数引用或内置标量函数...

...我认为它必须是常量，例如"10"，并且不能是变量或列名，不幸的是，这正是我想要做的。

这有效：

SELECT      PREFILTER.*
FROM        PREFILTER
INNER JOIN  GT1 ON FILTERED.GTAMT=GT1.LOSTAMT
ORDER BY    GTDATE DESC
FETCH FIRST 10 ROWS ONLY

这不起作用：

SELECT      PREFILTER.*
FROM        PREFILTER
INNER JOIN  GT1 ON PREFILTER.GTAMT=GT1.LOSTAMT
ORDER BY    GTDATE DESC
FETCH FIRST (GT1.LOSTAMT) ROWS ONLY    <=== changed here

收到以下错误：

SQL Error [428H7]: [SQ20467] Expression containing LOSTAMT must calculate a constant value.

此查询旨在查找在具有相当松散规范化和引用完整性（或缺乏）的较大表中孤立或错误键入的金融交易记录。

PREFILTER是一个返回主表子集的GT1查询，是另一个计算这些记录的更小更复杂子集的查询。然后JOIN它们，并返回(n)由 column 指定的行LOSTQTY，按日期降序排列。所以它应该只返回 (n) 个最近的记录。

请注意，我确实认识到我的位置FETCH不正确，并且它（或它演变成的任何东西）可能必须移动到 CTE 查询之一，例如GT1.

此外，在这个早期阶段，很明显我得到了一些笛卡尔结果，但一旦FETCH问题得到解决，这可能会得到解决。

作为参考，这里是项目的完整 SQL：

WITH  --SET THE INITIAL ACCOUNT & DATE RANGE  
        PREFILTER    AS  (   SELECT      *
                            FROM        GLTRANT
                            WHERE       GTDATE > 20170000
                            AND         GTACCT=112068
                        ),
      --CREATE LIST OF ALL POSITIVE VALUES
        POSVALS     AS  (   SELECT      GTAMT      AS POSAMT, COUNT(GTAMT) AS POSC
                            FROM        PREFILTER 
                            WHERE       GTAMT > 0
                            GROUP BY    GTAMT
                        ),
      --CREATE LIST OF ALL NEGATIVE VALUES, WITH SIGN DROPPED
        NEGVALS     AS  (   SELECT      ABS(GTAMT) AS NEGAMT, COUNT(GTAMT) AS NEGC
                            FROM        PREFILTER 
                            WHERE       GTAMT < 0
                            GROUP BY    ABS(GTAMT)
                        ),
      --CALCULATE DISCREPANCIES BETWEEN THE TWO LISTS. SUBTRACT THE TWO AND MULTIPLY THE SIGN BY THE ABSOLUTE VALUE 
      --OF THE DIFFERENCE. THEN TO RESTORE THE SIGN, MULTIPLY THE AMOUNT BY THE SIGN OF THE DIFFERENCE. 
        FOJ         AS  (   SELECT      SIGN(COALESCE(POSC,0)-COALESCE(NEGC,0))*COALESCE(POSAMT,NEGAMT) AS LOSTAMT, 
                                        ABS (COALESCE(POSC,0)-COALESCE(NEGC,0))                         AS LOSTQTY
                            FROM        POSVALS 
                            FULL OUTER JOIN NEGVALS ON POSAMT=NEGAMT
                            WHERE       COALESCE(POSC,0)-COALESCE(NEGC,0) <> 0
                        ),
      --GET DISCREPANCIES WITH COUNT >1 
        GT1         AS  (   SELECT      *
                            FROM        FOJ
                            WHERE       LOSTQTY>1 
                        )

--SEARCH PREFILTER FOR EACH AMOUNT (LOSTAMT) IN GT1 AND RETURN THE MOST RECENT (LOSTQTY) RECORDS 
SELECT      PREFILTER.*
FROM        PREFILTER
INNER JOIN  GT1 ON PREFILTER.GTAMT=GT1.LOSTAMT
ORDER BY    GTDATE DESC
FETCH FIRST (GT1.LOSTQTY) ROWS ONLY --DOES NOT WORK

标签： db2db2-400

解决方案

您应该能够为此使用窗口函数ROW_NUMBER：

WITH  --SET THE INITIAL ACCOUNT & DATE RANGE  
        PREFILTER    AS  (  
          SELECT  T.*,
                  ROW_NUMBER() OVER (ORDER BY T.GTDATE DESC) AS RN
          FROM (
                            SELECT      *
                            FROM        GLTRANT
                            WHERE       GTDATE > 20170000
                            AND         GTACCT=112068
          ) T
        ),
...
--SEARCH PREFILTER FOR EACH AMOUNT (LOSTAMT) IN GT1 AND RETURN THE MOST RECENT (LOSTQTY) RECORDS 
SELECT      PREFILTER.*
FROM        PREFILTER
INNER JOIN  GT1 ON PREFILTER.GTAMT=GT1.LOSTAMT
WHERE RN <= GT1.LOSTQTY
ORDER BY    GTDATE DESC

PREFILTER它以 的降序为每一行分配连续的数字GTDATE，您以后可以使用它来限制结果集。

推荐阅读