php - 如何修复mysql子查询显示

Question

SELECT User.username,
       IFNULL(SUM(credit) - SUM(debit), 0) AS total,
       ( SELECT SUM(Bid.credit) - SUM(Bid.debit) AS freebids
           FROM users AS User
           LEFT
           JOIN bids AS Bid
             ON Bid.user_id = User.id
          WHERE Bid.type = 2
       ) AS FreeBids,
       ( SELECT SUM(Bid.credit) - SUM(Bid.debit) AS Normalbids
           FROM users AS User
           LEFT
           JOIN bids AS Bid
             ON Bid.user_id = User.id
          WHERE Bid.type = 0
             OR Bid.type = 1
       ) AS NormalBids
  FROM users AS User
  LEFT
  JOIN bids AS Bid
    ON Bid.user_id = User.id
 GROUP
    BY User.id

这是我的示例表:对于用户

id username 
1  user1
2  user2
3  user3
4  user4

出价

id user_id debit credit type
1  1       0     10     0
2  1       1     5      2 
3  1       1     0      2
4  3       0     10     0
6  2       0     10     0
7  4       1     10     0
8  4       1     0      1

但是我在显示子查询时遇到问题(Freebids 和 Normalbids)都具有相同的值这里是示例显示:

username    total   FreeBids    NormalBids
user1       10       12809          965
user2       20       12809          965
user3       9        12809          965
user4       0        12809          965

我不知道我的查询去了哪里。有什么建议可以解决我的问题吗？感谢您的帮助。

Answer 1

之所以出现所有相同的值，是因为外部查询中的users 表和内部查询中的users 表之间没有区别。他们都有相同的别名。您必须通过为其分配不同的别名(例如 u)来区分外部 users 表或内部 users 表，以便子查询知道要为外部值引用哪个表:

... FROM users AS u ...

话虽如此，使用这样的子查询是非常低效的，因为它们需要为 users 中的each 行执行表(我们说的是整个表需要连接和过滤的次数与用户 x2 的次数一样多)。

使用条件聚合可以更有效地重写您的查询:

SELECT
    a.username,
    IFNULL(SUM(b.credit) - SUM(b.debit), 0) AS total,
    SUM(IF(b.type = 2, b.credit, 0)) - SUM(IF(b.type = 2, b.debit, 0)) AS freebids,
    SUM(IF(b.type IN (0,1), b.credit, 0)) - SUM(IF(b.type IN (0,1), b.debit, 0)) AS normalbids
FROM users a
LEFT JOIN bids b ON a.id = b.user_id
GROUP BY a.id, a.username

这基本上是说:仅当类型为 x 值时才对贷方/借方求和，并且您可以 SUM/COUNT/AVG/等。在任何情况下，您只想使用一个表连接。

---

SQL-Fiddle Demo