我有两个数据库,一个数据库包含我所有的图片,比如:
Database 1 name: images
-----+----------------------------+----------------------+-----------+------------+------------
| id | description | tags | imagename | resolution | location |
-----+----------------------------+----------------------+-----------+------------+------------
| 1 | We standing at eiffeltower | france, green, blue | IMG01.JPG | 1280x1024 | /img/2020 |
| 2 | We standing at bridge | france, orange, grey | IMG02.JPG | 1280x1024 | /img/2020 |
Database 2 name tagTranslations (for Dutch translation)
-----+--------+-----------------------
| id | tag | translation |
-----+--------+-----------------------
| 1 | france | frankrijk |
| 2 | orange | oranje. |
| 3 | grey | grijs. |
| 4 | green | groen |
| 5 | blue | blauw |
现在我想用 1 个 mysql 查询得到这样的结果:">
我们站在埃菲尔铁塔", "法国, 绿色, 蓝色", "IMG01.JPG", ">1280x1024", "/img/2020", "弗兰克里克", "格罗恩", "布劳"我们站在桥上", "法国, 橙色, 灰色", "IMG02.JPG">
, "1280x1024", "/img/2020", "弗兰克里克", "奥拉涅", "格里斯">
您首先应该努力修复数据模式L 每个图像标记都应存储在不同行的单独表中。正如您开始看到的那样,将分隔列表存储在许多邪恶的根源的数据库列中。更多关于这一点的信息可以在这篇著名的SO帖子中阅读。
也就是说,您可以使用与find_in_set()
和group_concat()
一起使用相关的子查询:
select
i.id,
i.description,
(
select group_concat(
tt.translation
order by find_in_set(tt.tag, replace(i.tags, ', ', ','))
separator ', '
)
from tagTranslations tt
where find_in_set(tt.tag, replace(i.tags, ', ', ','))
) tags,
i.imagename,
i.resolution,
i.location
from images i
相关子查询从转换表中检索行,其tag
可在相应images
行的tags
列表中找到。为此,我们方便地使用 MySQL 函数find_in_set()
(我们需要删除逗号后面的空格才能使函数正常工作(;然后,聚合函数group_concat()
重新生成一个分隔的翻译列表,再次使用find_in_set()
来遵循标签的原始顺序。
DB小提琴上的演示:
ID | 描述 | 标签 | 图像名称 | 分辨率 | 位置 -: |:------------------------- |:----------------------- |:-------- |:--------- |:-------- 1 |我们站在埃菲尔铁塔 |弗兰克赖克、格罗恩、布劳 |IMG01.JPG |1280x1024 |/img/2020 2 |我们站在桥上 |弗兰克赖克、奥拉涅、格里斯 |IMG02.JPG |1280x1024 |/img/2020
试试下面的代码:
CREATE VIEW table_comb AS
SELECT * FROM images
UNION ALL
SELECT * FROM tagTranslations