数据
我有一组数据，格式如下：
CAR_INVENTORY TABLE
CAR_ID MAKE_MODEL COLOR YEAR
1 Ford Fusion Black 2015
2 Tesla Model S White 2014
3 Acura ILX Blue 2013
4 Ford Fusion Black 2013
5 Toyota Corolla Blue 2014
6 Ford Fusion Blue 2013
7 Toyota Corolla Blue 2012
8 Acura ILX Black 2015
9 Ford Focus Blue 2012
10 Ford Fusion White 2013
11 Acura ILX Black 2012
12 Toyota Corolla Black 2015
13 Toyota Corolla Blue 2014
14 Ford Focus White 2015
15 Tesla Model S Red 2015
16 Acura TLX White 2014
17 Toyota Corolla Blue 2014
18 Ford Focus Black 2013
INVENTORY_LOG TABLE
LOG_ID CAR_ID NOTE
1 7 Issue with Fuel Guage
2 3 Sweet Ride
3 16 Zippy
4 14 Issue with transmission
5 3 Fun to Drive
6 2 *NULL*
7 8 *NULL*
8 10 Economic
9 15 WOW
10 9 Good Fuel Economy
11 16 Minor issue with Shifting
12 7 Issue with Airbag
13 17 Great Mileage
14 1 Nice Tech
15 13 *NULL*
16 11 Trunk is small
17 12 *NULL*
18 2 Very Speedy
19 7 Good Mileage
20 10 Roomy
21 4 *NULL*
22 6 Nice Tech Package
23 5 Good Economy
24 18 Cool
我知道这不完全正常。假设我不能处理数据。
car_inventory表对每辆库存汽车都有一行。inventory_log表对于car_inventory中列出的每辆车至少有一个条目，因此每辆车可能有许多日志条目。库存日志中的条目可以为空。
到目前为止我所做的
如果一辆车有一个写着“问题”的日志，它需要这样做。我想出来了：
SELECT
ci.car_id,
CONCAT(ci.color, " ", ci.make_model) as car,
SUM(IF (LOWER(il.note) LIKE '%issue%', TRUE, FALSE)) AS issue
FROM car_inventory ci
LEFT JOIN inventory_log il USING (car_id)
GROUP BY ci.car_id
ORDER BY ci.car_id;
今年：
car_id car issue
1 Black Ford Fusion 0
2 White Tesla Model S 0
3 Blue Acura ILX 0
4 Black Ford Fusion 0
5 Blue Toyota Corolla 0
6 Blue Ford Fusion 0
7 Blue Toyota Corolla 2
8 Black Acura ILX 0
9 Blue Ford Focus 0
10 White Ford Fusion 0
11 Black Acura ILX 0
12 Black Toyota Corolla 0
13 Blue Toyota Corolla 0
14 White Ford Focus 1
15 Red Tesla Model S 0
16 White Acura TLX 1
17 Blue Toyota Corolla 0
18 Black Ford Focus 0
对于任何有问题的车都会给出非零的结果。
接下来我要做的是统计所有的颜色，超过一年。假设我们只对黑色、白色和蓝色感兴趣，而且我们只有福特、讴歌、丰田和特斯拉（我知道我可以用一个准备好的声明来使其充满活力）。把那个也放在包里了：
SELECT
CASE
WHEN ci.make_model LIKE "Acura%" THEN "Acura"
WHEN ci.make_model LIKE "Ford%" THEN "Ford"
WHEN ci.make_model LIKE "Toyota%" THEN "Toyota"
WHEN ci.make_model LIKE "Tesla%" THEN "Tesla"
END AS Make,
SUM(CASE WHEN ci.color = "Black" THEN 1 ELSE 0 END) as Black,
SUM(CASE WHEN ci.color = "Blue" THEN 1 ELSE 0 END) as Blue,
SUM(CASE WHEN ci.color = "White" THEN 1 ELSE 0 END) as White
FROM car_inventory ci
LEFT JOIN inventory_log il USING (car_id)
WHERE
ci.year > 2012
GROUP BY Make
ORDER BY Make;
这给了我：
Make Black Blue White
Acura 1 1 1
Ford 3 1 2
Tesla 0 0 1
Toyota 1 3 0
快速清点汽车库存表，有14辆比2012年更新的汽车，分别是黑色、蓝色或白色。
问题
这就是我遇到麻烦的地方：
我想把两者结合起来。我需要按颜色计算所有的品牌，没有问题的地方。
这是我想要得到的结果集：
DESIRED RESULT
MAKE Black Blue White
Acura 1 1 0
Ford 3 1 1
Tesla 0 0 1
Toyota 1 2 0
以下三辆车被移走：
car_id car issues
7 Blue Toyota Corolla 2
14 White Ford Focus 1
16 White Acura TLX 1
我试过在where子句中添加
AND SUM(IF (LOWER(il.note) LIKE '%issue%', TRUE, FALSE)) = 0。这会导致mysql错误1111“无效使用组函数”。我也试过
HAVING SUM(IF (LOWER(il.note) LIKE '%issue%', TRUE, FALSE)) != 0。结果是不正确的，只显示了特斯拉和丰田的行数。问题
如何在MySQL中创建一个交叉表查询，以使带有日志条目（来自inventory）且其中包含单词“issue”的cars（来自car_inventory）不被计算在内？
最佳答案
SELECT
CASE
WHEN ci.make_model LIKE "Acura%" THEN "Acura"
WHEN ci.make_model LIKE "Ford%" THEN "Ford"
WHEN ci.make_model LIKE "Toyota%" THEN "Toyota"
WHEN ci.make_model LIKE "Tesla%" THEN "Tesla"
END AS Make,
SUM(CASE WHEN ci.color = "Black" THEN 1 ELSE 0 END) as Black,
SUM(CASE WHEN ci.color = "Blue" THEN 1 ELSE 0 END) as Blue,
SUM(CASE WHEN ci.color = "White" THEN 1 ELSE 0 END) as White
FROM car_inventory ci
WHERE
(ci.year > 2012) and
(ci.car_id not in (select distinct il.car_id from inventory_log il where il.note like '%issue%'))
GROUP BY Make
ORDER BY Make;