THE DATA
I have a set of data in the following format:
我有以下格式的一组数据:
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
I know it's not entirely normalized. Let's assume I can't mess with the data.
我知道它并没有完全正常化。让我们假设我不能搞乱这些数据。
The car_inventory table has one row for each car in stock. The inventory_log table has at least one entry for each car listed in car_inventory, so each car may have many log entries. The entries in inventory_log can be null.
car_inventory表每个库存有一排。 inventory_log表至少包含car_inventory中列出的每辆车的一个条目,因此每辆车可能有许多日志条目。 inventory_log中的条目可以为null。
WHAT I'VE DONE SO FAR
我做了什么
If a car has a log with the word 'issue' in it, it needs to be marked as such. I've figured that part out:
如果汽车上有一个带有“问题”字样的日志,则需要将其标记为此类。我认为那部分:
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;
This yealds:
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
Which gives a non-zero result for any car with issues.
对于任何有问题的汽车,这给出了非零结果。
The next thing I need to do is tally all makes by their color, beyond a certain year. Let's assume we're only interested in Black, White and Blue and we only have Ford's, Acura's, Toyota's and Tesla's (I know I can use a prepared statements to make that dynamic). Got that one in the bag too:
我需要做的下一件事就是根据他们的颜色计算所有品牌,超过一年。让我们假设我们只对黑色,白色和蓝色感兴趣,我们只有福特,讴歌,丰田和特斯拉(我知道我可以使用准备好的陈述来制作这种动态)。袋子里也有一个:
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;
This gives me:
这给了我:
Make Black Blue White
Acura 1 1 1
Ford 3 1 2
Tesla 0 0 1
Toyota 1 3 0
Doing a quick count of the car_inventory table, there are 14 cars newer than 2012, that are black, blue or white.
快速计算car_inventory表,有比2012更新的14辆车,分别是黑色,蓝色或白色。
THE PROBLEM
Here's where I'm having trouble:
这是我遇到麻烦的地方:
What I'm trying to do is combine the two. I need to count all makes by color, where there are no issues.
我想要做的就是将两者结合起来。我需要按颜色计算所有品牌,没有问题。
Here's the result set I'm trying to get:
这是我想要获得的结果集:
DESIRED RESULT
MAKE Black Blue White
Acura 1 1 0
Ford 3 1 1
Tesla 0 0 1
Toyota 1 2 0
With the following three cars removed:
随后拆除了以下三辆车:
car_id car issues
7 Blue Toyota Corolla 2
14 White Ford Focus 1
16 White Acura TLX 1
I've tried adding AND SUM(IF (LOWER(il.note) LIKE '%issue%', TRUE, FALSE)) = 0 to the where clause. This causes mysql error 1111 "Invalid use of group function".
我已经尝试将AND SUM(IF(LOWER(il.note)LIKE'%issue%',TRUE,FALSE))= 0添加到where子句。这导致mysql错误1111“无效使用组功能”。
I've also tried HAVING SUM(IF (LOWER(il.note) LIKE '%issue%', TRUE, FALSE)) != 0. It yealds incorrect results, only showing the rows for Tesla, and Toyota.
我也试过HAVING SUM(IF(LOWER(il.note)LIKE'%issue%',TRUE,FALSE))!= 0.它结果不正确,只显示特斯拉和丰田的行。
QUESTION
How do I create a crosstab query in MySQL, such that cars (from car_inventory) with log entries (from inventory_log) that have the word 'issue' in them are not counted?
如何在MySQL中创建交叉表查询,以使具有日志条目(来自inventory_log)且其中包含“问题”一词的汽车(来自car_inventory)不计算在内?
2 个解决方案
#1
1
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;
#2
0
So I've figured out how to do this with a SubQuery:
所以我已经想出如何使用SubQuery做到这一点:
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 (
SELECT
ci2.car_id,
ci2.make_model,
ci2.color
FROM car_inventory ci2
LEFT JOIN inventory_log il2 USING (car_id)
WHERE
ci2.year > 2012
GROUP BY ci2.car_id
HAVING SUM(IF (LOWER(il2.note) LIKE '%issue%', TRUE, FALSE)) = 0
) as ci
GROUP BY Make
ORDER BY Make;
This gives me:
这给了我:
Make Black Blue White
Acura 1 1 0
Ford 3 1 1
Tesla 0 0 1
Toyota 1 3 0
I'm not gonna mark this as the accepted answer, because I'm quite sure there's a better, more performant way to do this without subqueries.
我不会将此标记为已接受的答案,因为我非常确定在没有子查询的情况下执行此操作会有更好,更高效的方法。
#1
1
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;
#2
0
So I've figured out how to do this with a SubQuery:
所以我已经想出如何使用SubQuery做到这一点:
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 (
SELECT
ci2.car_id,
ci2.make_model,
ci2.color
FROM car_inventory ci2
LEFT JOIN inventory_log il2 USING (car_id)
WHERE
ci2.year > 2012
GROUP BY ci2.car_id
HAVING SUM(IF (LOWER(il2.note) LIKE '%issue%', TRUE, FALSE)) = 0
) as ci
GROUP BY Make
ORDER BY Make;
This gives me:
这给了我:
Make Black Blue White
Acura 1 1 0
Ford 3 1 1
Tesla 0 0 1
Toyota 1 3 0
I'm not gonna mark this as the accepted answer, because I'm quite sure there's a better, more performant way to do this without subqueries.
我不会将此标记为已接受的答案,因为我非常确定在没有子查询的情况下执行此操作会有更好,更高效的方法。