使用PDO对链接的2个表进行MySQL查询

gopyfrb3  于 2023-01-20  发布在  Mysql
关注(0)|答案(1)|浏览(117)

我在MySQL中有这2个表:
___Photos

|--------|-----------|-------------|
| AMP_Id | AMP_Photo | AMP_Formats |
|--------|-----------|-------------|
|      1 |   dog.jpg |         1,2 |
|      1 |   cat.jpg |           3 |
|--------|-----------|-------------|

___Formats

|--------|------------|
| AMF_Id | AMF_Format |
|--------|------------|
|      1 |      10x10 |
|      2 |      30x30 |
|      3 |      40x40 |
|--------|------------|

如何列出___Formats中链接格式的每张照片?
例如,对于上面显示的表,我需要类似这样的东西:

|-----------|-------------|
| AMP_Photo |  AMF_Format |
|-----------|-------------|
|   dog.jpg | 10x10,30x30 |
|   cat.jpg |       40x40 |
|-----------|-------------|

这是我迄今为止所尝试的:

SELECT
    AMP_Photo,
    IFNULL(GROUP_CONCAT(AMF_Format), "") as list_formats
FROM ___Photos i
LEFT JOIN ___Formats f
    ON f.AMF_Id = AMF_Format
GROUP BY AMP_Photo
ORDER BY AMP_Photo ASC
bvjveswy

bvjveswy1#

可以使用concat执行以下操作:

SELECT
    AMP_Photo,
    IFNULL(GROUP_CONCAT(AMF_Format), "") as list_formats
FROM ___Photos i
INNER JOIN ___Formats f ON i.AMP_Formats like concat('%',f.AMF_Id,'%')
GROUP BY AMP_Photo
ORDER BY AMP_Photo ASC

此处演示:https://dbfiddle.uk/eiQIgI8f

相关问题