檢索尚未分配到工作的車輛司機的數據

在過去的 24 小時內為某事而苦苦掙扎。請需要一些天才來發光。我正在嘗試為驅動程序顯示每週工作列表/日誌，它將顯示：

• 尚未分配給司機的工作
• 已分配給司機的工作
• 如果已將工作分配給司機，則顯示當天可用的司機
• 如果當天沒有工作，則顯示所有可用驅動程序的列表

最後兩點是我真正苦苦掙扎的地方。

我有以下表格：

drivers ('driver_id', 'driver_name')

vehicles ('vehicle_id', 'vehicle_make', 'vehicle_model')

jobs ('job_id', 'collection_address', 'collection_datetime', 'delivery_address', 'deliver_datetime', 'driver_id', 'vehicle_id') 

我附上了下面的輸出螢幕：

例如，如果您查看 2016 年 9 月 23 日，司機 Keith 正在工作，但 Nick 有空。但是，如果我進行子查詢，它會顯示兩個驅動程序，這不是正確的結果。下面是我的 SQL 語句，如果有人能提供幫助，我將不勝感激。

   SELECT listofdays.job_date, j.job_id, IF( j.driver_id > 0 AND j.job_id > 0, (SELECT driver_name FROM t_drivers WHERE driver_id = j.driver_id LIMIT 1), '') as job_driver, IF( j.vehicle_id > 0, (SELECT vehicle_reg FROM t_vehicles WHERE vehicle_id = j.vehicle_id LIMIT 1), 'no') as job_vehicle, j.collection_town, j.collection_postcode, j.delivery_town, j.delivery_postcode, j.job_status
FROM (
 SELECT '2016-09-19' + INTERVAL seq.seq DAY AS job_date
 FROM seq_0_to_999999 AS seq
 WHERE seq.seq <= TIMESTAMPDIFF(DAY , '2016-09-19', '2016-09-25' )
) AS listofdays
LEFT JOIN t_jobs j ON listofdays.job_date = DATE(j.collection_datetime)
ORDER BY DATE(listofdays.job_date), j.job_order ASC

我也很高興在速度和性能方面對上述查詢進行任何改進。它將用於內部系統。

修改後的查詢如下：

   SELECT
 sub.job_date,
 j.job_id,
 IFNULL(d.driver_name, '') as job_driver,
 IFNULL(v.vehicle_reg, 'no') as job_vehicle,
 j.collection_town,
 j.collection_postcode,
 j.delivery_town,
 j.delivery_postcode,
 j.job_status,
 IF(j.job_id IS NULL, sub.available_drivers, 'no availability') AS available_drivers
FROM
 (
   SELECT
     listofdays.job_date,
     GROUP_CONCAT(d.driver_name) AS available_drivers
   FROM
     (
       SELECT
         '2016-09-19' + INTERVAL seq.seq DAY AS job_date
       FROM
         seq_0_to_999999 AS seq
       WHERE
         seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
     ) AS listofdays
     CROSS JOIN t_drivers AS d
     LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
     AND d.driver_id = j.driver_id                   
GROUP BY listofdays.job_date
 ) AS sub
 LEFT JOIN t_jobs AS j ON sub.job_date = DATE(j.collection_datetime)
 LEFT JOIN t_drivers AS d ON j.driver_id = d.driver_id
 LEFT JOIN t_vehicles AS v ON j.vehicle_id = v.vehicle_id
ORDER BY
 job_date ASC,
 job_order ASC

輸出如下：

如果您在 23 日查看上面的圖片，則有 2 個空缺職位，其中應該有可用驅動程序的列表，但它表示沒有可用的驅動程序。在我們的例子中，尼克那天沒有工作，但它說沒有空位。

我將從可用驅動程序列表開始。由於您似乎希望它們作為 CSV 字元串，因此使用分組並將名稱與 GROUP_CONCAT() 連接是有意義的。使用作業的外連接到日期和驅動程序的叉積，然後，為了獲取每天可用的驅動程序，僅當驅動程序名稱沒有匹配的作業時才對它進行組連接：

SELECT
 listofdays.job_date,
 GROUP_CONCAT(IF(j.job_id IS NULL, d.driver_name, NULL)) AS available_drivers
FROM
 (
   SELECT
     '2016-09-19' + INTERVAL seq.seq DAY AS job_date
   FROM
     seq_0_to_999999 AS seq
   WHERE
     seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
 ) AS listofdays
 CROSS JOIN t_drivers AS d
 LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
                    AND d.driver_id = j.driver_id
GROUP BY
 listofdays.job_date
;

下一步也是最後一步是將上面的表用作派生表並t_jobs再次對其進行外連接——這次是獲取作業詳細資訊（您還需要外連接t_drivers並t_vehicle從這些表中獲取詳細資訊以及）：

SELECT
 sub.job_date,
 j.job_id,
 IFNULL(d.driver_name, '') as job_driver,
 IFNULL(v.vehicle_reg, 'no') as job_vehicle,
 j.collection_town,
 j.collection_postcode,
 j.delivery_town,
 j.delivery_postcode,
 j.job_status,
 sub.available_drivers
FROM
 (
   SELECT
     listofdays.job_date,
     GROUP_CONCAT(IF(j.job_id IS NULL, d.driver_name, NULL)) AS available_drivers
   FROM
     (
       SELECT
         '2016-09-19' + INTERVAL seq.seq DAY AS job_date
       FROM
         seq_0_to_999999 AS seq
       WHERE
         seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
     ) AS listofdays
     CROSS JOIN t_drivers AS d
     LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
                        AND d.driver_id = j.driver_id
   GROUP BY
     listofdays.job_date
 ) AS sub
 LEFT JOIN t_jobs AS j ON sub.job_date = DATE(j.collection_datetime)
 LEFT JOIN t_drivers AS d ON j.driver_id = d.driver_id
 LEFT JOIN t_vehicles AS v ON j.vehicle_id = v.vehicle_id
ORDER BY
 job_date ASC,
 job_order ASC
;

引用自：https://dba.stackexchange.com/questions/150570