带有三个表的SQL Join - SQL Join with three tables

昨天我已经测试了一个查询好几个小时了，但一直没有成功。这是三张表：

USERS:
#### id: 1 ##### name: Admin ##### Hometown: The Hague
POSTS:
#### id: 1 ##### userid: 1 ##### title: Test I ##### opinion: agree
#### id: 2 ##### userid: 1 ##### title: Nope.. ##### opinion: disagree
REACTIONS:
#### id: 1 ##### userid: 1 ##### opinion: agree
#### id: 2 ##### userid: 1 ##### opinion: disagree

这就是我想要的：我想要用户的基本信息（姓名、家乡等），我想统计这个人发布了多少赞美（意见后：同意）、多少抱怨（意见后，不同意）、有多少正面反应（意见后）和有多少负面反应（意见前：不同意）。

这是我现在使用的查询：

    SELECT
        u.name, u.hometown,
        SUM(IF(r.opinion="disagree",1,0)) AS agrees
        SUM(IF(r.opinion="disagree",1,0)) AS disagrees,
        SUM(IF(p.opinion="agree",1,0)) AS compliments,
        SUM(IF(p.opinion="disagree",1,0)) AS complaints
    FROM
        users AS u
    LEFT JOIN
        reactions AS r
    ON
        r.userid = u.id
    LEFT JOIN
        posts AS p
    ON
        p.userid = u.id
    WHERE
        u.id = 1

问题是，这并没有给我正确的信息。它返回8个阳性反应的值，尽管DB中只有两个反应。

我认为这与GROUP BY p.id，r.id有关，但我试过了，但没有成功。。。有人能启发我吗？

提前感谢！

这不是分组，连接是将一个表中的多个记录连接到另一个表的单个记录。这会导致重复。

例如，对于用户表中的单个条目，您可能在反应中有3条响应，在帖子中有3个响应。您的查询返回了9条记录，因为该用户的所有反应都与该用户的全部帖子相关联。。。

userid | reaction_id | post_id
  1           1           1
  1           1           2
  1           1           3
  1           2           1
  1           2           2
  1           2           3
  1           3           1
  1           3           2
  1           3           3

这意味着你需要将反应与帖子分开汇总。。。

SELECT
    u.name, u.hometown,
    r.agrees
    r.disagrees,
    p.compliments,
    p.complaints
FROM
    users AS u
LEFT JOIN
(
  SELECT
    userid,
    SUM(IF(r.opinion="agree",1,0)) AS agrees
    SUM(IF(r.opinion="disagree",1,0)) AS disagrees,
  FROM
    reactions
  GROUP BY
    userrid
)
  AS r
    ON r.userid = u.id
LEFT JOIN
(
  SELECT
    userid,
    SUM(IF(p.opinion="agree",1,0)) AS compliments,
    SUM(IF(p.opinion="disagree",1,0)) AS complaints
  FROM
    posts
  GROUP BY
    userid
)
  AS p
    ON p.userid = u.id
WHERE
    u.id = 1

根据您的DBMS，您可能可以执行以下操作：

SELECT
    *,
    (SELECT COUNT(*) FROM POSTS WHERE POSTS.userid = USERS.id and opinion = 'agree') compliments,
    (SELECT COUNT(*) FROM POSTS WHERE POSTS.userid = USERS.id and opinion = 'disagree') complaints,
    (SELECT COUNT(*) FROM REACTIONS WHERE REACTIONS.userid = USERS.id and opinion = 'agree') positive_reactions,
    (SELECT COUNT(*) FROM REACTIONS WHERE REACTIONS.userid = USERS.id and opinion = 'disagree') negative_reactions
FROM USERS

怎么样？

select id, name, hometown, sum(agrees) agrees, sum(disagrees) disagrees,
  sum(compliments) compliments, sum(complaints) complaints
from (
  select u.id, u.name, u.hometown,
    if(p.opinion = 'agree', 1, 0) agrees,
    if(p.opinion = 'disagree', 1, 0) disagrees,
    0 compliments, 0 complaints
  from users u
  left join posts p on u.id = p.userid
  union all
  select u.id, u.name, u.hometown,
    0, 0,
    if(r.opinion = 'agree', 1, 0),
    if(r.opinion = 'disagree', 1, 0)
  from users u
  left join reactions r on u.id = r.userid
) as S
group by id, name, hometown

select users.name, users.hometown, myPosts.compliments, myPosts.complaints,        myReaction.agrees, myReaction.disagrees
from Users users
LEFT JOIN
(
    select post.userid as userid
         , SUM(CASE WHEN post.opinion = 'agree' THEN 1 END) as compliments
         , SUM(CASE WHEN post.opinion = 'disagree' THEN 1 END) as complaints
    from Posts post
    group by post.userid
) as myPosts
on users.id = myPosts.userid
LEFT JOIN
(
    select reaction.userid as userid
     , SUM(CASE WHEN reaction.opinion = 'agree' THEN 1 END) as agrees
     , SUM(CASE WHEN reaction.opinion = 'disagree' THEN 1 END) as disagrees
    from Reaction reaction
    group by reaction.userid
) as myReaction
on users.id = myReaction.userid