如何在MySQL/SQL中按颜色合并汽车库存总数？

Question

我们有两家汽车商店，每家商店有12种不同型号的汽车。这些汽车有不同的型号和颜色。也有一些相同颜色，相同型号的汽车。

这就是为什么我想根据颜色数来统计两家商店的所有汽车。“纽约小提琴”如下：

样本小提琴数据库在这里：

我在上面的链接中有查询，但是查询只计算CompanyA或CompanyB总数。我怎样才能在我们的库存中找到总计的汽车和颜色呢？

谢谢你的帮助。

Answer 1

8.0&fiddle=b74786421c932727b5c3200e20c759a6

你的桌子是这样吗？

create table cars(
id int
, store varchar(50)
,model varchar(50)
,color varchar(50)
);

然后添加相同的数据：

(1,'storeA','Mercedes','Green')
,(2,'storeA','Kia','Grey')
,(3,'storeA','Lexus','Yellow')
,(4,'storeA','Lexus','Green')
,(5,'storeA','Lexus','Pink')
,(6,'storeA','Lexus','Pink')
,(7,'storeA','Mazda','White')
,(8,'storeA','Mazda','White')
,(9,'storeA','Mazda','Grey')
,(10,'storeA','Mazda','Brown')
,(11,'storeA','Kia','Brown')
,(12,'storeA','Nissan','White')
,(13,'storeA','Nissan','White')
,(14,'storeA','Nissan','Purple')
,(15,'storeA','Subaru','Blue')
,(16,'storeA','Subaru','Green')
,(17,'storeA','Toyota','Blue')
,(18,'storeA','Toyota','Purple')
,(19,'storeA','Toyota','White')
,(20,'storeA','Toyota','Purple')
,(21,'storeA','Honda','White')
,(22,'storeA','BMW','Red')
,(23,'storeA','BMW','Purple')
,(24,'storeA','Ford','Yellow')
,(25,'storeA','Ford','Green')
,(26,'storeA','Ford','Yellow')
,(27,'storeA','Ford','Grey')
,(28,'storeA','Honda','Green')
,(29,'storeA','Honda','White')
,(30,'storeA','Honda','White')
,(31,'storeA','BMW','Pink')
,(32,'storeA','Honda','White')
,(33,'storeA','Hyundai','Pink')
,(34,'storeA','Hyundai','Green')
,(35,'storeA','Hyundai','Green')
,(36,'storeA','Hyundai','Green')
,(37,'storeA','Jeep','Purple')
,(38,'storeA','Jeep','Pink')
,(39,'storeA','Jeep','Purple')
,(40,'storeA','Jeep','Yellow')
,(41,'storeB','BMW','Green')
,(42,'storeB','Ford','Grey')
,(43,'storeB','Jeep','Yellow')
,(44,'storeB','Jeep','Green')
,(45,'storeB','Toyota','Pink')
,(46,'storeB','Toyota','Pink')
,(47,'storeB','Ford','White')
,(48,'storeB','Jeep','White')
,(49,'storeB','Nissan','Grey')
,(50,'storeB','Nissan','Brown')
,(51,'storeB','Ford','Brown')
,(52,'storeB','Ford','White')
,(53,'storeB','Mazda','White')
,(54,'storeB','Mazda','Purple')
,(55,'storeB','Honda','Blue')
,(56,'storeB','Honda','Green')
,(57,'storeB','Hyundai','Blue')
,(58,'storeB','Hyundai','Purple')
,(59,'storeB','Jeep','White')
,(60,'storeB','Lexus','Purple')
,(61,'storeB','Subaru','White')
,(62,'storeB','Mercedes','Red')
,(63,'storeB','Mercedes','Purple')
,(64,'storeB','Kia','Yellow')
,(65,'storeB','Kia','Green')
,(66,'storeB','Mazda','Yellow')
,(67,'storeB','Nissan','Grey')
,(68,'storeB','BMW','Green')
,(69,'storeB','Subaru','White')
,(70,'storeB','Subaru','White')
,(71,'storeB','Honda','Pink')
,(72,'storeB','Subaru','White')
,(73,'storeB','Toyota','Pink')
,(74,'storeB','Toyota','Green')
,(75,'storeB','Toyota','Green')
,(76,'storeB','Toyota','Green')
,(77,'storeB','Lexus','Purple')
,(78,'storeB','Lexus','Pink')
,(79,'storeB','Mazda','Purple')
,(80,'storeB','Toyota','Yellow')

然后，您可以通过存储将数据转换为颜色：

select store,
model,
 SUM(CASE WHEN color = 'Pink' THEN 1 ELSE 0 END) AS 'Pink',
         SUM(CASE WHEN color = 'Red' THEN 1 ELSE 0 END) AS 'Red',
         SUM(CASE WHEN color = 'Purple' THEN 1 ELSE 0 END) AS 'Purple',
         SUM(CASE WHEN color = 'Yellow' THEN 1 ELSE 0 END) AS 'Yellow',
         SUM(CASE WHEN color = 'Green' THEN 1 ELSE 0 END) AS 'Green',
         SUM(CASE WHEN color = 'Grey' THEN 1 ELSE 0 END) AS 'Grey',      
         SUM(CASE WHEN color = 'White' THEN 1 ELSE 0 END) AS 'White',
         SUM(CASE WHEN color = 'Brown' THEN 1 ELSE 0 END) AS 'Brown',
         SUM(CASE WHEN color = 'Blue' THEN 1 ELSE 0 END) AS 'Blue'
from cars
group by store,model

或跨商店：

select model,
 SUM(CASE WHEN color = 'Pink' THEN 1 ELSE 0 END) AS 'Pink',
         SUM(CASE WHEN color = 'Red' THEN 1 ELSE 0 END) AS 'Red',
         SUM(CASE WHEN color = 'Purple' THEN 1 ELSE 0 END) AS 'Purple',
         SUM(CASE WHEN color = 'Yellow' THEN 1 ELSE 0 END) AS 'Yellow',
         SUM(CASE WHEN color = 'Green' THEN 1 ELSE 0 END) AS 'Green',
         SUM(CASE WHEN color = 'Grey' THEN 1 ELSE 0 END) AS 'Grey',      
         SUM(CASE WHEN color = 'White' THEN 1 ELSE 0 END) AS 'White',
         SUM(CASE WHEN color = 'Brown' THEN 1 ELSE 0 END) AS 'Brown',
         SUM(CASE WHEN color = 'Blue' THEN 1 ELSE 0 END) AS 'Blue'
from cars
group by model