基于标签的 SQL 查询

2022-01-23 00:00:00 many-to-many sql subquery mysql

我已经有一段时间没有做任何 SQL 了，我不确定这个问题是否有一个简单的解决方案.我也是一个菜鸟.

Its been a while since I have done any SQL and I am not sure if this problem has an easy solution or not. Also I am a bit of a Noob.

我正在尝试建立一个图片库，允许用户使用标签来搜索图片，然后单击其他标签来优化搜索并减少结果数量，但我对查询有很大的问题参与.

I am trying to put together an image gallery that allows users to use tags in order to search for images and then click on additional tags to refine the search and lower the number of results but I am having a big issue with the queries involved.

这是我当前数据库结构的简化版本:

This is a simplified version of my current database structure:

(2个表和一个额外的多对多链接表)

( 2 tables with an additional Many-to-Many link table )

CREATE TABLE images(
   image_id INT(12) AUTO_INCREMENT,
   image_name VARCHAR(128),
   PRIMARY KEY(image_id)
)ENGINE= INNODB;

CREATE TABLE tags(
   tag_name VARCHAR(64) NOT NULL,
   PRIMARY KEY(tag_name)
)ENGINE= INNODB;

CREATE TABLE images_tags_link(
   image_id_fk INT(12),
   tag_name_fk VARCHAR(64) NOT NULL,
   PRIMARY KEY(image_id_fk,tag_name_fk),
   FOREIGN KEY(image_id_fk) REFERENCES images(image_id),
   FOREIGN KEY(tag_name_fk) REFERENCES tags(tag_name)
)ENGINE= INNODB;

样本数据:

 ===images===
 ___________________________       
| image_id |  image_name    |     
|----------|----------------|     
|     1    |  image_001.jpg |     
|     2    |  image_002.jpg |     
|     3    |  image_003.png |     
|     4    |  image_004.jpg |     
|     5    |  image_005.gif |     
 ---------------------------                                     
 ===tags===                                 
 _______________
|    tag_name   |
|---------------|
| Landscape     |
| Portrait      |
| Illustration  |
| Photo         |
| Red           |
| Blue          |
| Character     |
| Structure     |
 ---------------
===images_tags_link===
 ________________________________
| image_id_fk | tag_name_ fk     |
|-------------|------------------|
|      1      |    Landscape     |
|      1      |    Illustration  |
|      1      |    Blue          |
|      2      |    Blue          |
|      2      |    structure     |
|      2      |    Landscape     |
|      3      |    Illustration  |
|      4      |    Red           |
|      4      |    Portrait      |
|      4      |    Character     |
|      5      |    Blue          |
|      5      |    Photo         |
 --------------------------------

我的问题在于以下查询:

My Problem is with the following Query:

我正在寻找一个查询，该查询可以从 IMAGES 表中选择所有图像名称"，其中包含所有用户列出的标签，例如，用户可以搜索蓝色"和风景"标签，它应该只输出image_names 'image_001.jpg' AND 'image_002.jpg'.

I am looking for a single Query that can select all 'image_names' from the IMAGES table that have all the users listed tags, for example a user may search for the 'Blue' AND 'Landscape' tags which should only output the image_names 'image_001.jpg' AND 'image_002.jpg'.

===输入===

用户选择的标签:( 'Blue' , 'Landscape' )

The Users chosen tags: ( 'Blue' , 'Landscape' )

===输出===

具有所有列出的标签的图像名称:('image_001.jpg'，'image_002.jpg')

The image_names that have ALL the listed tags: ( 'image_001.jpg' , 'image_002.jpg' )

提前致谢.

推荐答案

2个简单的方法.

以下任意一种，取决于所需的列、可能的标签数量等.

Either of the following, depending on columns required, number of possible tags, etc.

SELECT *
FROM images
INNER JOIN images_tags_link a ON images.image_id = a.image_id_fk AND a.tag_name_fk = 'Blue'
INNER JOIN images_tags_link b ON images.image_id = b.image_id_fk AND b.tag_name_fk = 'Landscape'


SELECT images.image_id, images.image_name, COUNT(*) AS tag_count
FROM images
INNER JOIN images_tags_link a ON images.image_id = a.image_id_fk 
WHERE a.tag_name_fk IN ('Blue', 'Landscape')
GROUP BY images.image_id, images.image_name
HAVING tag_count = 2

相关文章