计算联接内的列

Question

除了在 select 语句中使用函数之外，当涉及到两个以上的表时，我真的很困惑连接！

我有三个表users，data和projects。data上的每一行都唯一地与users表的用户相关，data表的外键是uid列，该列引用users表的id。

到现在还不难吗？但我希望。

表中的每个项目projects都有两列名为starterfinisher它们也引用users表id列上，并且彼此不同。

那又怎样！我想获取每个用户的数据，包括他们的data以及他们启动它(如starter)或完成它(如finisher)的projects数

select 
d . *,
u . *,
COUNT(p.starter) as starter,
COUNT(p.finisher) as finisher
from
users u
left join
projects p ON u.id = p.freelancer
left join
data d ON u.id = d.uid
GROUP BY (u.id)

此查询导致所有用户(如预期的那样)都是starter或finisher或根本没有(projects上没有相应的行)但是计数结果不正确！ 例如，ID 为1的用户已完成一个项目并启动了三个项目，但结果显示starter列和finisher列的1和1！

Answer 1

使用子查询，您可以计算每个用户成为starter和finisher的次数。

SELECT u.id
,s.countStarter
,f.countFinisher
FROM users u
LEFT JOIN (
SELECT starter, 
COUNT(*) AS countStarter
FROM projects 
GROUP BY starter
) s ON u.id = s.starter
LEFT JOIN (
SELECT finisher, 
COUNT(*) AS countFinisher
FROM projects 
GROUP BY finisher
) f ON u.id = f.finisher

SQLFiddle

Answer 2

您加入错误的字段。 此方法在连接在一起之前按starter和finisher进行总结：

select d.*, u.*,
coalesce(sum(ps.cnt), 0) as starter,
coalesce(SUM(pf.cnt), 0) as finisher
from users u left join
(select p.starter, count(*) as cnt
from projects p
group by p.starter
) ps
on ps.starter = u.id left join
(select p.finisher, count(*) as cnt
from projects p
group by p.finisher
) pf
on pf.finisher = u.id left join
data d
ON u.id = d.uid
GROUP BY (u.id);

Answer 3

这是你要找的：

SELECT *, id as userid,
(SELECT COUNT(*) FROM
projects
WHERE   projects.starter = userid
OR      projects.finisher = userid
)AS number
FROM users
LEFT JOIN data
ON users.id = data.uid

我已经在我的数据库上快速测试了它，它就像一个魅力:)。希望对你有帮助