从子查询结果获取限制值

发布于 2025-01-18 23:40:08 字数 226 浏览 2 评论 0原文

我想在查询中使用 LIMIT 选项，但预期行数存储在另一个表中。这就是我所拥有的，但它不起作用：

select * from table1 limit (select limitvalue from table2 where id = 1)

当我只运行子查询时，结果是 6，正如预期的那样。

如果可能的话，我更喜欢使用WITH语句，但这也不起作用。先感谢您！

原文

I would like to use the LIMIT option in my query, but the number of expected rows is stored in another table. This is what I have, but it doesn't work:

select * from table1 limit (select limitvalue from table2 where id = 1)

When I only run the subquery, the result is 6, as expected.

I prefer working with a WITH statement if possible, but that didn't work eiter.
Thank you in advance!

分享到QQ

分享到微博

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

发布评论

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

菊凝晚露 2025-01-25 23:40:08

您可以使用准备好的语句来获取来自其他表的查询限制，因为限制子句不允许非常量变量作为参数：

PREPARE firstQuery FROM "SELECT * FROM table1 LIMIT ?";
SET @limit = (select limitvalue from table2 where id = 1);
EXECUTE firstQuery USING @limit;

sql查询的来源来自另一篇文章

You could use a prepared statement to get the limit of queries from the other table because the limit clause does not allow non constant variables as parameter:

PREPARE firstQuery FROM "SELECT * FROM table1 LIMIT ?";
SET @limit = (select limitvalue from table2 where id = 1);
EXECUTE firstQuery USING @limit;

The source of the sql query from another post

回复收藏 0 原文

赴月观长安 2025-01-25 23:40:08

您可以在 CTE 中使用 MariaDB 的 ROW_NUMBER 函数来计算要输出的行数，并将其与限制值进行比较。例如：

WITH rownums AS (
  SELECT *,
         ROW_NUMBER() OVER () AS rn
  FROM table1
)
SELECT *
FROM rownums
WHERE rn <= (SELECT limitvalue FROM table2 WHERE id = 1)

注意使用LIMIT而不使用ORDER BY不能保证每次都能得到相同的结果。您应该在 ROW_NUMBER 窗口函数的 OVER 部分包含 ORDER BY 子句。对于我的演示中的示例数据，您可以使用以下内容：

ROW_NUMBER() OVER (ORDER BY mark DESC)

dbfiddle 上的演示

You can make use of MariaDB's ROW_NUMBER function in a CTE to count the rows to be output, comparing that against the limitvalue. For example:

WITH rownums AS (
  SELECT *,
         ROW_NUMBER() OVER () AS rn
  FROM table1
)
SELECT *
FROM rownums
WHERE rn <= (SELECT limitvalue FROM table2 WHERE id = 1)

Note Using LIMIT without ORDER BY is not guaranteed to give you the same results every time. You should include an ORDER BY clause in the OVER part of the ROW_NUMBER window function. With the sample data in my demo, you might use something like: