mysql存储过程查询建议

Question

我有一个存储过程，它接受一些参数，其中一个参数是“汽车名称”列表。例如

CALL `car_list_inventory`(1,"'Honda','Nissan','BMW','Toyota'");

存储过程的结果返回当前可用的汽车名称，例如，如果我要运行该程序，结果将是

store  cars_available   
----  ----------------          
1      Honda, BMW

确定可用汽车的查询部分是

SELECT store, GROUP_CONCAT(DISTINCT cars.model) AS cars_available 
FROM cars 
JOIN cars_avail ON cars.id = cars_avail.id_ref
WHERE cars.id = LAST_INSERT_ID();

我想要做的是添加第二部分，该部分将返回不可用的汽车，例如，整个结果应如下所示

 store  cars_available    cars_not_available
 -----  ----------------   ---------------- 
    1      Honda, BMW        Nissan, Toyota 

这可能吗？

Answer 1

要获取不可用的汽车:

SELECT store, GROUP_CONCAT(DISTINCT cars.model) AS cars_not_available
FROM cars
LEFT JOIN cars_avail ON cars.id = cars_avail.id_ref
WHERE cars_avail.id_ref IS NULL
GROUP BY store

您还可以使用NOT IN:

SELECT store, GROUP_CONCAT(DISTINCT cars.model) AS cars_not_aveilable
FROM cars
WHERE id NOT IN (SELECT id_ref FROM cars_avail)
GROUP BY store

要在单个查询中获取两者，您可以执行以下操作:

SELECT store,
       GROUP_CONCAT(CASE WHEN cars_avail.id_ref IS NOT NULL
                         THEN cars.model
                    END) AS cars_available,
       GROUP_CONCAT(CASE WHEN cars_avail.id_ref IS NULL
                         THEN cars.model
                    END) AS cars_not_available
FROM cars
LEFT JOIN cars_avail ON cars.id = cars_avail.id
GROUP BY store