我有一个表“通道”。
channelId
a
b
c
d
表格“视频”
videoId | channelId
1 | a
2 | b
3 | c
4 | e
表“评论”
commentID | videoID | videoID_channelID
xx | 1 | a
yy | 2 | b
zz | 5 | e
tt | 6 | f
关键是:
我需要:
因此,我想执行3条SQL语句,每个引用另一个表的表2。
我用双重内部联接(https://www.sqlitetutorial.net/sqlite-inner-join/)进行了尝试,但似乎返回了所有适合的组合,而不是:
channelId
a
b
videoId | channelId
1 | a
2 | b
commentID | videoID | videoID_channelID
xx | 1 | a
yy | 2 | b
到目前为止,我的代码可以使所有频道至少包含1个视频和1条评论:
SELECT
channel.channelId
FROM
channel
INNER JOIN video ON video.channelId = channel.channelId
INNER JOIN comment ON comment.videoID_channelID = video.channelId
您可以通过连接所有3个表的相同查询来获得所需的所有结果,但是对于每种情况,请选择不同的列:
SELECT c.channelId
FROM channel c
INNER JOIN video v ON v.channelId = c.channelId
INNER JOIN comment cm ON cm.videoID_channelID = v.channelId;
SELECT v.videoID, c.channelId
FROM channel c
INNER JOIN video v ON v.channelId = c.channelId
INNER JOIN comment cm ON cm.videoID_channelID = v.channelId;
SELECT cm.commentID, v.videoID, c.channelId
FROM channel c
INNER JOIN video v ON v.channelId = c.channelId
INNER JOIN comment cm ON cm.videoID_channelID = v.channelId;
如果实际数据中有重复项,则可能必须在每个SELECT之后添加DISTINCT。
参见演示。
结果:
| channelId |
| --------- |
| a |
| b |
| videoID | channelId |
| ------- | --------- |
| 1 | a |
| 2 | b |
| commentID | videoID | channelId |
| --------- | ------- | --------- |
| xx | 1 | a |
| yy | 2 | b |
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句