MySQL交叉表/数据透视聚合.根据其他表中的列删除计数 [英] MySQL Crosstab / Pivot Aggregation. Removing counts based on column in other table
问题描述
数据
我有一组以下格式的数据:
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.
car_inventory表中的每辆库存车都有一行.对于在car_inventory中列出的每辆汽车，stockstock_log表至少具有一个条目，因此每辆汽车可能具有许多日志条目.库存日志中的条目可以为空.
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.
我做的如此遥远
如果汽车中有一个带有问题"一词的原木，则需要对其进行标记.我已经弄清楚了那一部分:
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;
这让人惊讶:
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.
接下来我要做的是根据特定年份的颜色来统计所有品牌.假设我们只对黑色，白色和蓝色感兴趣，而我们只有福特，Ac歌，丰田和特斯拉(我知道我可以使用准备好的语句来使之动态).也把那个放在袋子里:
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;
这给了我
Make Black Blue White
Acura 1 1 1
Ford 3 1 2
Tesla 0 0 1
Toyota 1 3 0
快速查看car_inventory表，发现有14辆比2012年新的汽车，它们是黑色，蓝色或白色.
Doing a quick count of the car_inventory table, there are 14 cars newer than 2012, that are black, blue or white.
问题
这是我遇到麻烦的地方:
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
移除了以下三辆车:
car_id car issues
7 Blue Toyota Corolla 2
14 White Ford Focus 1
16 White Acura TLX 1
我尝试将AND SUM(IF (LOWER(il.note) LIKE '%issue%', TRUE, FALSE)) = 0添加到where子句.这将导致mysql错误1111无效使用组函数".
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".
我也尝试过HAVING SUM(IF (LOWER(il.note) LIKE '%issue%', TRUE, FALSE)) != 0.它预示着错误的结果，只显示了特斯拉和丰田的行.
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.
问题
如何在MySQL中创建交叉表查询，以使不计入其中具有单词"issue"的汽车(来自car_inventory)和具有日志条目(来自ventory_log)的汽车?
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?
推荐答案
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;
这篇关于MySQL交叉表/数据透视聚合.根据其他表中的列删除计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
