MySQL 限制解决方法

Question

我需要根据百分比限制记录，但 MYSQL 不允许这样做。我需要 (count(User Id)/max(Total_Users_bynow) 的 10% User Id我的代码如下:

select * from flavia.TableforThe_top_10percent_of_the_user where `User Id` in (select distinct(`User Id`) from flavia.TableforThe_top_10percent_of_the_user group by `User Id` having count(distinct(`User Id`)) <= round((count(`User Id`)/max(Total_Users_bynow))*0.1)*count(`User Id`));

请帮忙。

Answer 1

考虑将您的问题分解。您可以使用用户变量来获取您需要的内容。 Quoting from this question's answers :

You don't have to solve every problem in a single query.

所以...让我们完成这件事。我不会提供完整的查询，但会提供一些示例:

-- Step 1. Get the total of the rows of your dataset
set @nrows = (select count(*) from (select ...) as a);
-- --------------------------------------^^^^^^^^^^
-- The full original query (or, if possible a simple version of it) goes here

-- Step 2. Calculate how many rows you want to retreive
-- You may use "round()", "ceiling()" or "floor()", whichever fits your needs
set @limrows = round(@nrows * 0.1);

-- Step 3. Run your query:
select ...
limit @limrows;

<罢工>

<小时/>

查了一下，发现this post这说明我的上述方法行不通。然而，还有一个替代方案:

-- Step 1. Get the total of the rows of your dataset
set @nrows = (select count(*) from (select ...) as a);
-- --------------------------------------^^^^^^^^^^
-- The full original query (or, if possible a simple version of it) goes here

-- Step 2. Calculate how many rows you want to retreive
-- You may use "round()", "ceiling()" or "floor()", whichever fits your needs
set @limrows = round(@nrows * 0.1);

-- Step 3. (UPDATED) Run your query. 
--         You'll need to add a "rownumber" column to make this work.
select *
from (select @rownum := @rownum+1 as rownumber
           , ... -- The rest of your columns
      from (select @rownum := 0) as init
         , ... -- The rest of your FROM definition
      order by ... -- Be sure to order your data
     ) as a
where rownumber <= @limrows

希望这会有所帮助(我认为这次它会正常工作)