MySQL查询根据引用其他表的位置从表中计数

Question

SELECT s.id, u.name, de.name,
SUM(dp.destination_id = 1 AND l.liked = 1) AS tokyo_count,
SUM(dp.destination_id = 2 AND l.liked = 1) AS osaka_count,
SUM(dp.destination_id = 3 AND l.liked = 1) AS hokkaido_count
FROM `t0120_swipe_set` s, `t0121_swipe_log` l, `t0111_destination_photo` dp, `t0110_destination` d,`t0101_user` u, 
`t0110_destination` de
    WHERE s.id = 8
        AND s.id = l.set_id
            AND l.destination_photo_id = dp.id
                AND dp.destination_id = d.id
                    AND s.user_id = u.id
                        AND s.suggested_destination_id = de.id;

t0110_destination
    id
    name
    swipe_count

t0111_destination_photo
    id
    destination_id

t0120_swipe_set
    id
    user_id
    suggested_destination_id

t0121_swipe_log
    set_id
    user_id
    destination_photo_id
    liked

这些是我的查询和表结构，我想要得到的是每个滑动组在每个目的地的点赞总数。这个查询是可执行的，但是，它总是只返回一条记录。比方说，我想从所有滑动集中选择，而不是 s.id = 8。

附言。我试过 IN，但是 SUM 搞砸了，而且它也只在行上返回。

我能解决这个问题吗？谢谢

Answer 1

SELECT s.id, u.name, de.name,
SUM(dp.destination_id = 1 AND l.liked = 1) AS tokyo_count,
SUM(dp.destination_id = 2 AND l.liked = 1) AS osaka_count,
SUM(dp.destination_id = 3 AND l.liked = 1) AS hokkaido_count
FROM `t0120_swipe_set` s
INNER JOIN `t0121_swipe_log` l ON s.id = l.set_id
INNER JOIN `t0111_destination_photo` dp ON l.destination_photo_id = dp.id
INNER JOIN `t0110_destination` d ON dp.destination_id = d.id
INNER JOIN `t0101_user` u ON s.user_id = u.id
INNER JOIN `t0110_destination` de ON s.suggested_destination_id = de.id
GROUP BY S.ID

刚刚尝试了上面的代码。希望这会有所帮助。