基于标签的SQL查询 [英] Tag based SQL query

问题描述

已经有一段时间了，因为我已经完成了所有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"，例如，一个用户可以搜索"Blue"和"Landscape"标签，这些标签仅应输出image_names'image_001.jpg'和'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'.

===输入===

用户选择的标签:(蓝色"，风景")

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

这篇关于基于标签的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！