MySQL-百分比限制? [英] Mysql - LIMIT by Percentage?

问题描述

说，我在选择查询"中定义了一个别名"count"，我想将返回的数量限制为count/5(或表的20％).

Say I define an alias 'count' in my Select Query and I want to limit the amount returned to count / 5 (or 20% of the table).

我该怎么做? Mysql似乎只接受整数而不是函数.

How can I do this? Mysql doesn't seem to take anything but integers, not functions.

推荐答案

正确. LIMIT子句使用偏移量和行数，而不是百分比.您正在考虑支持SELECT TOP 20 PERCENT ...的Microsoft SQL Server(请注意，在标准SQL中均未指定LIMIT或TOP).

Correct. The LIMIT clause takes an offset and a count of rows, not a percentage. You're thinking of Microsoft SQL Server, which supports SELECT TOP 20 PERCENT ... (note that neither LIMIT or TOP are specified in standard SQL).

我将在两个查询中执行此操作:

I would do this in two queries:

SELECT COUNT(*) FROM MyTable WHERE ...conditions...

SELECT * FROM MyTable WHERE ...conditions... ORDER BY ...order... LIMIT ?

将参数?替换为count/5.

Replace the parameter ? with the count / 5.

您不必在单个查询中解决所有问题.

这篇关于MySQL-百分比限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！