为什么我的查询显示没有错误，但也返回零行？(Why my query shows no errors, but also returns zero rows?)

我编写了这个查询，以获得与用户一样多的行数+每个用户创建的潜在数量+已转换的所有潜力。这是它的样子：

SELECT u.*, p.allPotentials, pc.cPotentials FROM os_user u JOIN (SELECT FID_author, count(*) allPotentials FROM os_potential) p ON p.FID_author = u.ID JOIN (SELECT converted, FID_author, count(*) cPotentials FROM os_potential) pc ON p.FID_author = u.ID AND pc.converted = 1

我试图用不相关的子查询做这个，因为这个答案解释了我，我可以将我的查询组合成1.但我得到0行。

我的表看起来像这样：

用户：

+----+------+-------+ | ID | Name | Email | +----+------+-------+

潜力：

+----+------+-------+------------+-----------+ | ID | Name | Email | FID_author | converted | +----+------+-------+------------+-----------+

FID_author是外键，用户ID。

我的查询返回0行并且没有显示错误。我究竟做错了什么？

编辑

到目前为止我的查询：

SELECT u.*, p.allPotentials, pc.cPotentials FROM os_user u LEFT JOIN (SELECT FID_author, count(*) allPotentials FROM os_potential GROUP BY FID_author) p ON p.FID_author = u.ID LEFT JOIN (SELECT converted, FID_author, count(*) cPotentials FROM os_potential GROUP BY FID_author) pc ON p.FID_author = u.ID AND pc.converted = 1 GROUP BY u.ID

我得到的结果几乎与预期的一样，但问题是， cPotentials每行包含1，这是错误的。有很多很多只有1.哪里可能是问题？

I have written this query to get as many rows as there are users + count of potentials that each user have created + all potentials that have been converted. This is how it looks like:

I am trying to do it with uncorrelated subquery as this answer explained me, that I can combine my queries into 1. But im getting 0 rows.

My tables looks like this:

Users:

+----+------+-------+ | ID | Name | Email | +----+------+-------+

Potentials:

+----+------+-------+------------+-----------+ | ID | Name | Email | FID_author | converted | +----+------+-------+------------+-----------+

FID_author is foreign key, the user id.

My query is returning 0 rows and shows no errors. What am I doing wrong?

EDIT

So far my query:

I am getting results almost as expected, but the problem is, cPotentials contains 1 in every row, which is false. There are much many then only 1. Where could be the problem?

最满意答案

子查询中缺少组，最后使用左连接

SELECT u.*, p.allPotentials, pc.cPotentials FROM os_user u LEFT JOIN (SELECT FID_author, count(*) allPotentials FROM os_potential GROUP BY FID_author) p ON p.FID_author = u.ID LEFT JOIN (SELECT converted, FID_author, count(*) cPotentials FROM os_potential GROUP BY converted,FID_author) pc ON pc.FID_author = u.ID AND pc.converted = 1

Missing group by on subquery and eventully use left join

SELECT u.*, p.allPotentials, pc.cPotentials FROM os_user u LEFT JOIN (SELECT FID_author, count(*) allPotentials FROM os_potential GROUP BY FID_author) p ON p.FID_author = u.ID LEFT JOIN (SELECT converted, FID_author, count(*) cPotentials FROM os_potential GROUP BY converted,FID_author) pc ON pc.FID_author = u.ID AND pc.converted = 1

更多推荐