mysql - SQL 多连接整理

Question

我有这个声明:

SELECT board.*,numlikes 
FROM board
LEFT JOIN (
    SELECT pins.board_id, COUNT(source_user_id) AS numlikes
    FROM likes 
    INNER JOIN pins ON pins.id = likes.pin_id 
    GROUP BY pins.board_id
) likes ON board.id = likes.board_id
WHERE who_can_tag='' 
ORDER BY numlikes DESC 
LIMIT 10

然后我可以使用 ".$info['board_name']." 从 board 中提取行。但是，我不擅长多重连接，除了 board 和 likes 表之外，我还需要将另一个表连接到此表。第三个表是user，匹配到board.user_id和user.user_id。

然后我如何使用此数据从 user 中提取用户名？

不会 ".$info['username']." 仍然在表 board 中搜索字段 username 吗？

Answer 1

这会做你想做的:

SELECT board.*,
    `user`.username,
    numlikes 
FROM board
INNER JOIN `user` ON board.user_id = `user`.user_id
LEFT JOIN (
    SELECT pins.board_id, COUNT(source_user_id) AS numlikes
    FROM likes 
    INNER JOIN pins ON pins.id = likes.pin_id 
    GROUP BY pins.board_id
) likes ON board.id = likes.board_id
WHERE who_can_tag='' 
ORDER BY numlikes DESC 
LIMIT 10