mysql - 连接两个几乎相同的没有空值的 sql 结果行

Question

我试图从这个 sql 查询中获取 currentManager 和 beforeManager，但是我猜我的 IF 语句做错了。有一个 sql 查询：

SELECT number, year_made, model,
IF(car_management.date_to >= NOW(), users.name, null) AS currentManager,
IF(car_management.date_to < NOW(), users.name, null) AS beforeManager,
statuses.name AS statusName
FROM cars
LEFT JOIN car_management ON car_management.cars_id = cars.id
LEFT JOIN users ON users.id = car_management.user_id
LEFT JOIN car_status ON car_status.cars_id = cars.id
LEFT JOIN statuses ON statuses.id = car_status.status_id

我得到的结果是 NULL 的 where IF 语句返回 else，结果如下：

sql查询结果照片

我需要将这 4 行加入两个而不是 NULL 与加入的值。

有人对此问题有任何解决方案吗？

标签： mysqlsqlif-statementjoinleft-join

Answer 1

为了减少行，您可以尝试使用（假）聚合函数 max() 和 group by

    SELECT number, year_made, model,
    max(IF(car_management.date_to >= NOW(), users.name, null)) AS currentManager,
    max(IF(car_management.date_to < NOW(), users.name, null)) AS beforeManager,
    statuses.name AS statusName
    FROM cars
    LEFT JOIN car_management ON car_management.cars_id = cars.id
    LEFT JOIN users ON users.id = car_management.user_id
    LEFT JOIN car_status ON car_status.cars_id = cars.id
    LEFT JOIN statuses ON statuses.id = car_status.status_id
    GROUP BY number, year_made, model, statuses.name