我在mysql数据库中有3个表，如下
表1：视频
id name
1 one
2 two
3 three
表2：标签
id name
1 stage=test
2 age=under18
表3:tags_to_items其中tags_id=tags.id和item_id=videos.id
tag_id item_id
1 1
2 2
2 1
1 3
这里item_id 1有2个标签。我想从videos表中选择tag_id=1和tag_id=2的所有记录。所以输出应该是video.id=1。
请帮我这样做，提前谢谢。
您真的不需要任何来自视频或加入的查询。只需做你目前需要的事情：
SELECT item_id as VIDEO_ID
SUM(CASE WHEN tag_id = 1 THEN 1 ELSE 0 END) as tag_1,
SUM(CASE WHEN tag_id = 2 THEN 1 ELSE 0 END) as tag_2
FROM tags_to items
WHERE tag_id = 1 or tag_id = 2
GROUP BY VIDEO_ID
HAVING tag_1>0 AND tag_2>0
SELECT * FROM videos WHERE id IN (SELECT item_id FROM tags_to_items WHERE tag_id IN (1,2) )
您可以这样做：
SELECT t1.tag_id, t1.item_id, t2.name AS tagName, t3.name AS videoName FROM tags_to_items AS t1
LEFT JOIN tags AS t2 ON t1.tag_id=t2.id
LEFT JOIN videos AS t3 ON t1.item_id=t3.id
WHERE t1.tag_id IN (1,2)