DB2查询：获取前10行并强制包含TABLE1、TABLE2

需求说明 #

需要按rows_read降序获取前10行数据，同时强制包含tabname为'TABLE1'和'TABLE2'的数据（即使这两张表不在前10行范围内）。

原查询语句及结果 #

原查询代码 #

db2 "select substr(a.tabname,1,30) as TABNAME,
a.rows_read as RowsRead,
(a.rows_read / (b.commit_sql_stmts + b.rollback_sql_stmts + 1)) as TBRRTX,
(b.commit_sql_stmts + b.rollback_sql_stmts) as TXCNT
from sysibmadm.snaptab a, sysibmadm.snapdb b
where a.dbpartitionnum = b.dbpartitionnum
and b.db_name = 'LIVE'
order by a.rows_read desc fetch first 10 rows only"

原查询结果 #

TABNAME                        ROWSREAD             TBRRTX               TXCNT
------------------------------ -------------------- -------------------- --------------------
XOUTMSGLOG                              43845129056                   41           1049571334
SCHSTATUS                               35336410261                   33           1049571334
ADDRESS                                 26817245226                   25           1049571334
CATGRPDESC                              25628156703                   24           1049571334
ORDERITEMS                              23945555619                   22           1049571334
ORDERS                                  10656700035                   10           1049571334
XPAYINSTDATA                            10555959906                   10           1049571334
OFFER                                   10426958061                    9           1049571334
SCHBRDCST                               10286981444                    9           1049571334
ATTRVALDESC                              8327058697                    7           1049571334

  10 record(s) selected.

修改后的查询语句 #

通过UNION ALL组合两个查询：第一个查询获取原前10行数据，第二个查询单独筛选'TABLE1'和'TABLE2'，并排除已经在前10行中的数据（避免重复），最后统一按RowsRead降序排序：

db2 "SELECT * FROM (
    -- 获取按rows_read降序的前10行
    select substr(a.tabname,1,30) as TABNAME,
        a.rows_read as RowsRead,
        (a.rows_read / (b.commit_sql_stmts + b.rollback_sql_stmts + 1)) as TBRRTX,
        (b.commit_sql_stmts + b.rollback_sql_stmts) as TXCNT
    from sysibmadm.snaptab a, sysibmadm.snapdb b
    where a.dbpartitionnum = b.dbpartitionnum
      and b.db_name = 'LIVE'
    order by a.rows_read desc fetch first 10 rows only
) AS top10
UNION ALL
-- 筛选TABLE1和TABLE2中不在前10行的数据
select substr(a.tabname,1,30) as TABNAME,
    a.rows_read as RowsRead,
    (a.rows_read / (b.commit_sql_stmts + b.rollback_sql_stmts + 1)) as TBRRTX,
    (b.commit_sql_stmts + b.rollback_sql_stmts) as TXCNT
from sysibmadm.snaptab a, sysibmadm.snapdb b
where a.dbpartitionnum = b.dbpartitionnum
  and b.db_name = 'LIVE'
  and a.tabname IN ('TABLE1', 'TABLE2')
  and a.tabname NOT IN (
      select substr(tabname,1,30)
      from sysibmadm.snaptab a, sysibmadm.snapdb b
      where a.dbpartitionnum = b.dbpartitionnum
        and b.db_name = 'LIVE'
      order by rows_read desc fetch first 10 rows only
    )
ORDER BY RowsRead DESC"

期望结果 #

TABNAME                        ROWSREAD             TBRRTX               TXCNT
------------------------------ -------------------- -------------------- --------------------
XOUTMSGLOG                              43845129056                   41           1049571334
SCHSTATUS                               35336410261                   33           1049571334
ADDRESS                                 26817245226                   25           1049571334
CATGRPDESC                              25628156703                   24           1049571334
ORDERITEMS                              23945555619                   22           1049571334
ORDERS                                  10656700035                   10           1049571334
XPAYINSTDATA                            10555959906                   10           1049571334
OFFER                                   10426958061                    9           1049571334
SCHBRDCST                               10286981444                    9           1049571334
ATTRVALDESC                              8327058697                    7           1049571334
TABLE1                                        81444                    1           10495713341
TABLE2                                           97                    1           1049571334
 
 12 record(s) selected.

内容的提问来源于stack exchange，提问作者Tom123456