MySQL 使用 join 返回不正确的数据

Question

我已经加入了多个表。您可以在这个demo中找到表和MySQL查询。 。

这些是表格:

1. 用户

   id name
   1  abc
   2  xyz
   3  pqr
   

2. friend

   user_id friend_id
   1       2
   1       3
   2       3
   

3. 集合

   id user_id friend_id amount
   1  1        2         100
   2  2        1        -100
   3  2        3         200
   4  3        2        -200
   5  1        3         300 
   6  3        1        -300
   

4. 账单

   id use_id(bill_creator)
   1   1
   2   2
   3   2
   

5. bill_person

   id  bill_id user_id
   1  1        1
   2  1        2
   3  1        3
   4  2        2
   5  2        3
   6  3        2
   6  3        1
   

到目前为止我已经提出了这个查询:

SELECT mf.id
     , mf.name
     , c.amount AS amount,count(bp.user_id) AS no_common_bills
  FROM (
         SELECT fr.user_id AS user_id
              , fr.friend_id AS friend_id
           FROM friend fr
           JOIN users fru
             ON fru.id = fr.user_id
          WHERE fru.id IN (1)
          UNION
         SELECT fl.friend_id AS user_id
              , fl.user_id AS friend_id
           FROM friend fl
           JOIN users flf
             ON flf.id = fl.friend_id
          WHERE flf.id IN (1)
       ) f
  JOIN users mf
    ON mf.id = f.friend_id
  LEFT
  JOIN collection c
    ON c.friend_id = mf.id
   AND c.user_id = f.user_id
  LEFT JOIN bill_person bp 
    ON bp.user_id=f.user_id AND c.friend_id = mf.id
 GROUP BY mf.id
 ORDER BY mf.id

这个查询的输出是

id   NAME AMOUNT NO_COMMON_BILLS
2    XYZ   100    2
3    PQR   300    2

但我想要这个结果:

id   NAME AMOUNT NO_COMMON_BILLS
2    XYZ   100    2
3    PQR   300    1

我在 NO_COMMON_BILLS 处得到错误的输出。除此之外，所有值都是正确的。

Answer 1

第三次是魅力......

http://sqlfiddle.com/#!2/338dd/12

 SELECT u.name friend
      , COUNT(bp2.user_id) no_common_bills
   FROM users u
   LEFT 
   JOIN 
      ( SELECT user_id me, friend_id them FROM friend WHERE user_id = 1 
         UNION 
        SELECT friend_id,user_id FROM friend WHERE friend_id = 1
      ) x
     ON x.them = u.id
   LEFT
   JOIN bill_person bp1
    ON bp1.user_id = x.them
   LEFT
   JOIN bill_person bp2
     ON bp2.bill_id = bp1.bill_id
    AND bp2.user_id = x.me
  WHERE u.id <> 1
  GROUP 
     BY friend;

 FRIEND     NO_COMMON_BILLS
 jkl        0
 mno        0
 pqr        1
 xyz        2