用替换单词的一个查询查询两个表

尼古拉兹

我在mySql中有两个表，例如：

表1：城市

id    name     transport
1   new-york     1,3,4
2   dallas       3,4
3   la           1,2,4
4   california   3,4

表2：运输

id     name
1   bus
2   trolleybus
3   train
4   metro

我可以通过一个查询收到类似示例的结果吗？

结果：

id    name         transport
1   new-york     bus,train,metro
2   dallas       train,metro
3   la           bus,trolleybus,metro
4   california   train,metro

阿比克·查克拉博蒂（Abhik Chakraborty）

您应该更改数据库结构并对其进行规范化。切勿将数据存储为逗号分隔，因为这是存储数据的一种不好方法。但是，在修复数据库设计之前，以下查询应该可以完成您的工作。

select 
id,
name,
group_concat(transport)
from
(
  select
  c.id,
  c.name,
  t.transport as transport
  from city c
  join transport t on find_in_set(t.id,c.transport)

)x

group by id ;

演示

如果您需要订购运输价值，则可以使用

group_concat(transport ORDER BY transport)

为什么逗号分隔是不好的做法？您可以阅读以下内容，为什么要忽略它？在数据库列中存储分隔列表真的那么糟糕吗？

为了规范化数据库，您将需要创建另一个表，如下所示：

city_transport (cid int , tid) ;
cid = city id 
tid = transport id

对于每个城市，此表中都有多个条目。所以桌子应该像

create table city (id int , name varchar(100));
insert into city values
(1,'new-york'),(2,'dallas'),(3,'la'),(4,'california');

create table transport (id int ,transport varchar(100));
insert into transport values
(1,'bus'),(2,'trolleybus'),(3,'train'),(4,'metro');

create table city_transport (cid int ,tid int);

insert into city_transport values
(1,1),(1,3),(1,4),(2,3),(2,4),(3,1),(3,2),(3,4),(4,3),(4,4);

和获得相同结果的查询是

select
c.id,
c.name,
group_concat(t.transport order by t.transport) as transport
from city_transport ct
join city c on c.id = ct.cid
join transport t on t.id = ct.tid 
group by c.id ;

当您有大量数据时，从本质上讲，您将需要索引，然后在索引列上使用join，其性能将比使用带逗号分隔列表的find_in_set更好。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。