mysql用列名连接两个表

rslzwgfq 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(258)

我的mysql数据库中有以下表：
cars表：

+----Cars   Table-----+
+---------------------+
| id | BMW   | KIA    |-and another cars
+----+----------+-----+
| 1  | M5    | Rio    |
| 2  | Z1    | Serato |
| 3  | X5    | Sorento|

类似表格：

+----------------------+-----+
| id | Cars_id| Cars   |ip   |
+----+----------+------+-----+
| 1  |   1    | KIA    |1.0.1|
| 2  |   1    | BMW    |1.0.1|

基本上，cars表有更多的cars标记和它们的模型。
我的问题是，如何连接这些表-like.cars\u id=cars.id和like.cars=cars.column names？。
所以，我想问这样一个问题：

SELECT Cars.BMW, Cars.KIA,  COUNT(Like.ip) AS likes
FROM CARS_Table
    LEFT JOIN Like_table
        ON Cars.id = Like.Cars_id AND what?

sql mysql

来源：https://stackoverflow.com/questions/52024463/mysql-join-two-table-with-column-names

2条答案

按热度按时间

6yjfywim1#

如果你只想数汽车型号ip
那么两个表之间就不需要连接，下面的查询将计算模型ip

select Cars,  count(ip) from Like_table    
group by Cars

赞(0）回复(0）举报 2021-06-21

holgip5t2#

你应该这样规范化这个表

Brand 
id, name  
1, BMW 
2, KIA 
3, ...

cars 
id, id_brand, car_name 
1, 1, M5 
2, 1, Z1 
3, 1, X5 
4, 2, Rio 
5, 2, Serato 
6, 2, Sorento 

like_table  

id, cars_id, ip 
1, 1, 1.0.1 
2, 1, 1.0.1 

select b.name, c.name, count(ip)
from  like_table l 
inner join  cars c on l.cars_id = c.id 
inner join  Brand b on b.id = c.id_brand
group by b.name, c.name

这样很容易得到喜欢的汽车，但也喜欢的品牌和其他

select b.name count(ip)
from  like_table l 
inner join  cars c on l.cars_id = c.id 
inner join  Brand b on b.id = c.id_brand
group by b.name

赞(0）回复(0）举报 2021-06-20

我来回答

mysql用列名连接两个表

2条答案

相关问题

热门标签

最新问答