多对多SQL查询,用于选择带有某些单词标记的所有图像 [英] Many to Many SQL Query for selecting all Images Tagged with certain words
本文介绍了多对多SQL查询,用于选择带有某些单词标记的所有图像的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在Postgres中有2个表:
I have 2 Tables in Postgres:
CREATE TABLE "images" (
"id" serial NOT NULL PRIMARY KEY,
"title" varchar(300) NOT NULL,
"relative_url" varchar(500) NOT NULL)
和
CREATE TABLE "tags" (
"id" serial NOT NULL PRIMARY KEY,
"name" varchar(50) NOT NULL)
要在图像和标签之间建立多对多关系,我需要另外一张表:
To establish many to many relationship between images and tags I have another table as:
CREATE TABLE "tags_image_relations" (
"id" serial NOT NULL PRIMARY KEY,
"tag_id" integer NOT NULL REFERENCES "tags" ("id") DEFERRABLE INITIALLY DEFERRED,
"image_id" integer NOT NULL REFERENCES "images" ("id") DEFERRABLE INITIALLY DEFERRED)
现在,我必须编写一个查询,例如"选择所有标记有'apple','microsoft'和'google'的图像的relative_url"
Now I have to write a query like "select relative_url of all images tagged with 'apple' and 'microsoft' and 'google' "
对此最优化的查询有哪些?
What can the most optimized query for this?
推荐答案
这是我编写的有效查询:
Here's the working query I wrote:
SELECT i.id, i.relative_url, count(*) as number_of_tags_matched
FROM images i
join tags_image_relations ti on i.id = ti.image_id
join tags t on t.id = ti.tag_id
where t.name in ('google','microsoft','apple')
group by i.id having count(i.id) <= 3
order by count(i.id)
此查询将首先显示与所有三个标签匹配的图像,然后显示与3个标签中的至少2个匹配的图像,最后显示与1个标签匹配的图像.
This query will first show the images matching all three tags, then the images matching at least 2 of the 3 tags, finally at least 1 tag.
这篇关于多对多SQL查询,用于选择带有某些单词标记的所有图像的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文