mysql - 在sql中查找三个表的平均值

Question

我创建了三个表

路线

routeNo decimal
routeName varchar

//每条路线有多个站点，每个站点都存储为纬度/经度

停止:

routeNo decimal
stopNo decimal

latitude decimal
longitude decimal

和

//order有一条有停靠点的路线

订单配送:

routeNo decimal
stopNo decimal
orderNo decimal

//每个司机都分配了一条路线和一辆车

工作日:

workday date
driver char(9)
route decimal 4,0
vehicle char(7)

我必须找出所有司机在哪几天停靠的次数超过每日平均停靠次数(所有司机的停靠次数)

我知道我需要连接这三个表才能获得所有必需的信息。并且已经编码以找到每个司机最常访问的站点，因为我认为这将有助于找到他们可能超过平均站点数的位置。

到目前为止我的代码:

SELECT t1.driver, t2.stopNo, t1.maxStop
FROM
(
    SELECT t.driver, MAX(t.stopCount) AS maxStop
    FROM
    (
        SELECT w.driver, s.stopNo, COUNT(s.stopNo) AS stopCount
        FROM workday w INNER JOIN Stops s ON w.route = s.routeNo
        INNER JOIN orderDelivery od ON s.routeNo = od.routeNo AND s.stopNo =      od.stopNo
         GROUP BY w.driver, s.stopNo
    ) t
    GROUP BY t.driver
) t1
 INNER JOIN
(
    SELECT w.driver, s.stopNo, COUNT(s.stopNo) AS stopCount
    FROM workday w INNER JOIN Stops s ON w.route = s.routeNo
    INNER JOIN orderDelivery od ON s.routeNo = od.routeNo AND s.stopNo =      od.stopNo
  GROUP BY w.driver, s.stopNo
    ) t2
ON t1.driver = t2.driver AND t1.maxStop = t2.stopCount

Answer 1

这是一个刺:

select w.workday, count(*) as dailyCount
from
    workday w inner join
    stops s on s.routeNo = w.route inner join
    orderDelivery d on d.routeNo = s.routeNo and r.stopNo = s.routeNo
group by w.workday
having count(*) /* daily count */ > (
    select avg(dailyCount) /* average across all days for all drivers */
    from (
        select count(*) as dailyCount
        from
            workday w inner join
            stops s on s.routeNo = w.route inner join
            orderDelivery d on d.routeNo = s.routeNo and r.stopNo = s.routeNo
        group by w.workday
    ) dc
)