SELECT + SUM +左联接 [英] SELECT + SUM + LEFT JOIN

问题描述

我希望能得到一些建议.我需要弄清有多少个项目的合作伙伴分配比例不为100％.

I was hoping to receive some advice. I need to work out how many projects do not have partner allocations of 100%.

一个项目可以有很多合作伙伴.

One project can have many partners.

项目表和伙伴表通过ProjectID连接.

The project and partner tables are connected by the ProjectID.

我需要选择项目ID，项目名称FROM项目，其中partner_％！= 100％.

I need to select project id, project name FROM project where partner_% != 100%.

所以我一直在思考

SELECT project id, project name, SUM (partner_%) as [Project Partner Total]
FROM Project Table
  LEFT JOIN [partner table]
  ON project.projectID = partner.projectID

我上面的工作可以吗? (这是SQL Server 2014).

Would my the above work okay for this? (It is SQL Server 2014).

推荐答案

如果我理解正确，则需要group by和having子句:

If I understand correctly, you need a group by and a having clause:

SELECT project id, project name, SUM(partner_%) as [Project Partner Total]
FROM Project Table LEFT JOIN
     [partner table]
     ON project.projectID = partner.projectID
GROUP BY project id, project name
HAVING SUM(partner_%) <> 100;

这篇关于SELECT + SUM +左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！