如何通过MySQL中结果总数的百分比来限制查询结果？

The below mysql question returns only the 10 first rows. How can I limit the them to 10% of all?

下面的mysql问题只返回10个第一行。如何将它们限制在10%?

SELECT page, 
       poso, 
       diff 
FROM   (SELECT page, 
               Count(*) AS poso, 
               ( Sum(Date(timestamp) = Curdate()) - Sum( 
                 Date(timestamp) = Date_sub(Curdate(), 
                                   INTERVAL 1 day)) ) 
                        diff 
        FROM   `behaviour` 
        WHERE  Date(timestamp) >= Date_sub(Curdate(), INTERVAL 1 day) 
        GROUP  BY page 
        ORDER  BY ( Sum(Date(timestamp) = Curdate()) - Sum( 
                    Date(timestamp) = Date_sub(Curdate(), 
                               INTERVAL 1 day)) 
                  ) DESC 
        LIMIT  10) AS u 
ORDER  BY diff DESC 

1 个解决方案

#1

Adapted from the answer to the duplicate question:

改编自重复问题的答案:

SELECT page,
       poso,
       diff
FROM    (
    SELECT *,
           @counter := @counter + 1 AS counter
    FROM   (select @counter:=0) AS initvar, 
           (SELECT page, 
                   Count(*) AS poso, 
                   ( Sum(Date(timestamp) = Curdate()) - Sum( 
                     Date(timestamp) = Date_sub(Curdate(), 
                                       INTERVAL 1 day)) ) 
                            diff
            FROM   `behaviour` 
            WHERE  Date(timestamp) >= Date_sub(Curdate(), INTERVAL 1 day) 
            GROUP  BY page 
            ORDER  BY ( Sum(Date(timestamp) = Curdate()) - Sum( 
                        Date(timestamp) = Date_sub(Curdate(), 
                                   INTERVAL 1 day)) 
                      ) DESC) AS u
) AS v
WHERE counter <= 10/100 * @counter
ORDER  BY diff DESC;

Demo here: http://rextester.com/JKMBZR62923

在这里演示:http://rextester.com/JKMBZR62923