使用子 SELECT 查询中的 COUNT 值来确定 FETCH ROW 限制

发布于 2024-10-19 03:32:50 字数 418 浏览 6 评论 0原文

我很好奇是否有任何方法可以重写以下无效的 DB2 查询以获得我需要的内容：

WITH pull AS (
    SELECT *
    FROM email_list
), 
pull_count AS (
    SELECT count(email) as email_count
    FROM email_list
)

SELECT *
FROM pull
FETCH FIRST integer(email_count / 2) ROWS ONLY

我收到“Token INTEGER was not valid”。有效令牌：ROW ROWS' 错误。

我的目标是使用第二个WITH 语句pull_count 为第一个WITH 语句pull 设置FETCH 限制。我只想选择一半可用的行。

有什么建议吗？

原文

I'm curious to see if there's any way to rewrite the following invalid DB2 Query to get what I need:

WITH pull AS (
    SELECT *
    FROM email_list
), 
pull_count AS (
    SELECT count(email) as email_count
    FROM email_list
)

SELECT *
FROM pull
FETCH FIRST integer(email_count / 2) ROWS ONLY

I'm getting the 'Token INTEGER was not valid. Valid tokens: ROW ROWS' error.

My goal is to use the 2nd WITH statement, pull_count, to set the FETCH limit for the 1st WITH statement, pull. I'm only looking to SELECT for half of the rows available.

Any suggestions?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我一向站在原地 2024-10-26 03:32:50

正如您所发现的，FETCH FIRST 子句不能使用表达式。它仅接受非负整数。

没有一种很好的方法可以做到这一点，但可以通过使用 OLAP 函数来实现。这些可能不存在于您的 DB2 版本上（如果您使用的是 DB2 for Linux/UNIX/Windows，那么它们可能存在）。

假设您的表有电子邮件和姓名列：

with pull as (
   select 
      email, 
      name, 
      rownumber() over(order by email) as row, 
      count(email) over () as cnt
   from 
      email_list
)
select 
   email,
   name 
from 
   pull
where
   row <= cnt/2;

这可能表现不佳；事实上，简单地执行 2 个查询可能会更有效 - 一个执行计数，另一个获取您关心的特定行数。

The FETCH FIRST clause can't use an expression, as you've found. It accepts a non-negative integer only.

There is not a pretty way to do this, but it's possible by using OLAP functions. These may not be present on your version of DB2 (if you're on DB2 for Linux/UNIX/Windows then they are probably there).

Assuming your table has columns email and name:

with pull as (
   select 
      email, 
      name, 
      rownumber() over(order by email) as row, 
      count(email) over () as cnt
   from 
      email_list
)
select 
   email,
   name 
from 
   pull
where
   row <= cnt/2;

This may not perform very well; and in fact it may be more efficient to simply execute 2 queries -- 1 doing the count, and the other fetching the specific number of rows you care about.

回复收藏 0 原文

~没有更多了~