组由左连接表
问题描述:
计数这里是我的查询:组由左连接表
$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
时,它会弄乱一切。在assignments
表中有一列叫做id_lead
,我希望count()(delivered
)计算由id_lead
分组的分配总数,以便它忽略多于1行且具有相同的id_lead
。
答
这是你的意思吗? :
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
不同的非空值的数量。
(如果这不你的意思,那么请澄清。)
完善。谢谢。 – scarhand 2011-12-14 01:27:17