按左连接表中的计数进行分组

发布于 2024-12-21 02:40:42 字数 433 浏览 1 评论 0原文

这是我的查询：

$select d.*, 
count(a.id) as delivered
from `dealerships` as d
left join `assignments` as a on (a.id_dealership = d.id)
group by d.id
order by d.name asc

现在这可以工作，但它正在计算重复的潜在客户。当我将 a.id_lead 添加到分组依据时，它弄乱了一切。 taskments 表中有一个名为 id_lead 的列，我希望 count() (delivered) 来计算按 < 分组的作业总数code>id_lead，以便它忽略具有相同 id_lead 的多于 1 行。

原文

Here's my query:

$select d.*, 
count(a.id) as delivered
from `dealerships` as d
left join `assignments` as a on (a.id_dealership = d.id)
group by d.id
order by d.name asc

now this works, but it is counting duplicate leads. when I add a.id_lead to the group by, it messes up everything. There is a column in the assignments table called id_lead and I want the count() (delivered) to count the total of assignments grouped by id_lead, so that it ignores more than 1 row with the same id_lead.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

剩余の解释 2024-12-28 02:40:42

你是这个意思吗？：

select d.*, 
count(distinct a.id_lead) as delivered
from `dealerships` as d
left join `assignments` as a on (a.id_dealership = d.id)
group by d.id
order by d.name asc

它与您的查询相同，只是它不会计算 a 中的记录总数，而是仅计算不同的非空值的数量在a.id_lead中。

（如果这不是您的意思，请澄清。）

Is this what you mean? :

select d.*, 
count(distinct a.id_lead) as delivered
from `dealerships` as d
left join `assignments` as a on (a.id_dealership = d.id)
group by d.id
order by d.name asc

It's the same as your query, except that instead of counting the total number of records in a, it will only count the number of distinct non-null values in a.id_lead.

(If that's not what you mean, then please clarify.)

回复收藏 0 原文

~没有更多了~