1
seth19960929 2022-09-13 18:49:48 +08:00
select img_id from relations_table where tag_id='a' and tag_id='b' limit 1
|
2
j0hnj 2022-09-13 19:05:28 +08:00
create table images (id int, url varchar(100));
create table tags (id int, tag varchar(10)); create table image_tags (image_id int, tag_id int); insert into images values (1, 'image_1'), (2, 'image_2'), (3, 'image_3'); insert into tags values (1, 'tag_a'), (2, 'tag_b'); insert into image_tags values (1,1),(2,2),(3,1),(3,2); select T.id, T.url from (select images.id, images.url from images inner join image_tags on images.id = image_tags.image_id inner join tags on tags.id = image_tags.tag_id where tags.tag = 'tag_a') T inner join image_tags on T.id = image_tags.image_id inner join tags on tags.id = image_tags.tag_id where tags.tag = 'tag_b'; |
3
j0hnj 2022-09-13 19:28:44 +08:00
查到一种更简单的实现:
![image.png]( https://s2.loli.net/2022/09/13/mnwONS2dDyT1Ire.png) https://blog.huoding.com/2019/09/22/775 |
4
eason1874 2022-09-13 20:05:10 +08:00
经典场景,不会可以参考 WordPress 的关系表设计
|
5
FYFX 2022-09-13 20:14:27 +08:00
等价与含标签 a 的图片的表和含标签 b 的图片的表做 inner join
|