选择大于平均值的值

问题描述：

我有两个表，我需要找到交付的驱动程序数量超过所有驱动程序携带的包裹平均数量的驱动程序，并且要比平均数量更多.我要显示的第一个表是Package表，第二个是Truck表.我知道我需要使用一个计数，但是我无法弄清楚如何将其与平均值相结合

I have two tables and i need to find the drivers who have delivered more than the average number of packages carried by all drivers and how much more than the average. The first table i'm showing is the Package table and the 2nd is the Truck table. I know i need to use a count but i cannot figure out how to combine that with the average

Truck_no
103 
105 
102 
108 
108 
108 
101 
109 
109 
100 
100 
100 


Truck_no Drivername
100 JONES
101 DAVIS
102 GOMEZ
103 THOMPSON
104 HERSHEY
105 FERRIS
106 SHAVER
107 LEE
108 TOPI
109 ACKERMAN

到目前为止，这是我的查询

this is my Query so far

select drivername, count(package.truck_no) as PackageCount
from PACKAGE, truck
where truck.TRUCK_NO=PACKAGE.TRUCK_NO
group by drivername

我得到这些结果

Drivername      Packagecount
ACKERMAN    2
DAVIS           1
FERRIS          1
GOMEZ           1
JONES           3
THOMPSON    1
TOPI            3

我将非常感谢您的帮助.非常感谢

I would really appreciate any help. Thanks a lot

答

    SELECT drivername, packagecount
      FROM (   SELECT drivername, COUNT(1) AS packagecount
                 FROM truck
            LEFT JOIN package
                      ON truck.truck_no = package.truck_no
             GROUP BY 1) all_driver_counts
CROSS JOIN
           (SELECT AVG(n) AS avg_packagecount
              FROM (  SELECT truck_no, COUNT(1) AS n
                        FROM package
                    GROUP BY 1) truck_packages) the_average
     WHERE packagecount > avg_packagecount;

这将产生:

+------------+--------------+
| drivername | packagecount |
+------------+--------------+
| ACKERMAN   |            2 |
| JONES      |            3 |
| TOPI       |            3 |
+------------+--------------+

自下而上阅读上面的查询，我们计算每辆卡车交付的平均包裹数( the_average )，并将其与每个驾驶员的计数( all_driver_counts )合并，仅过滤那些驾驶员人数超过平均值的行.

Reading the above query bottom-up, we compute the average packages delivered per truck (the_average) and join that with each driver's counts (all_driver_counts), filtering only those rows WHERE a driver's count exceeds the average.

解析(SQL滑动窗口)功能可能使上面的查询更容易，但是，可惜，MySQL不支持它们.

Analytic (SQL sliding window) functions might have made the above query easier, but, alas, MySQL does not support them.

相关推荐