MySql:如何限制打印行数

Question

这是我的表sampletable4 (只是一个示例)。我的表有数百万的数据。

      app_id    latency    -- app_id is char(255), latency is float
    | 1111   |    0.940056 |
    | 1110   |    0.261509 |
    | 3211   |   0.0506242 |
    | 3210   |    0.468594 |
    | 3110   |    0.191095 |

其中app_id是字符串，延迟是浮点型。延迟衡量每个应用程序的API调用的速度。

我想打印出每个app_id的最慢(或最高延迟)。我的查询不限于最慢的5%。我希望将查询限制在每个app_id的最慢5%。

下面是我的问题：

  SELECT 
  latency  
  FROM sampleTable4
  WHERE app_id = '5697'
  ORDER BY latency DESC;

这是app_id = '5697‘的特殊情况。我如何更改它，使其打印出5%的延迟？

Answer 1

SELECT latency FROM  (SELECT latency,(SELECT FLOOR(COUNT(*)*0.05) AS per_count   
  FROM sampleTable4
  WHERE app_id = '5697'  ) AS 5per_count,IF(@cnt=NULL,@cnt:=1,@cnt:=@cnt+1) AS cnt 
  FROM sampleTable4 ,(SELECT @cnt:=0) AS T
  WHERE app_id = '5697'
  ORDER BY latency DESC ) FT       
  WHERE cnt<=5per_count