select DISTINCT(cust.id) as ttttt,u.user_id,u.display_name,cma.address
from crm_user as u
join crm_customer as cust
on u.user_id = cust.owner_id
and u.user_id = 10000
and cust.valid = 'Y'
join crm_cust_contact as cuc
on cust.id = cuc.cust_id
join crm_contact_mail as cum
on cuc.id = cum.contact_id
join crm_mail_address as cma
on cum.mail_address_id = cma.id
and cma.address like '%m%';已经对cust.id去重复了,为什吗还有重复的列?
from crm_user as u
join crm_customer as cust
on u.user_id = cust.owner_id
and u.user_id = 10000
and cust.valid = 'Y'
join crm_cust_contact as cuc
on cust.id = cuc.cust_id
join crm_contact_mail as cum
on cuc.id = cum.contact_id
join crm_mail_address as cma
on cum.mail_address_id = cma.id
and cma.address like '%m%';已经对cust.id去重复了,为什吗还有重复的列?
select ttttt,count(*) from (
select DISTINCT(cust.id) as ttttt,u.user_id,u.display_name,cma.address
from crm_user as u
join crm_customer as cust
on u.user_id = cust.owner_id
and u.user_id = 10000
and cust.valid = 'Y'
join crm_cust_contact as cuc
on cust.id = cuc.cust_id
join crm_contact_mail as cum
on cuc.id = cum.contact_id
join crm_mail_address as cma
on cum.mail_address_id = cma.id
and cma.address like '%m%'
)T
group by ttttt
select ttttt,count(*) from (
select DISTINCT(cust.id) as ttttt,u.user_id,u.display_name,cma.address
from crm_user as u
join crm_customer as cust
on u.user_id = cust.owner_id
and u.user_id = 10000
and cust.valid = 'Y'
join crm_cust_contact as cuc
on cust.id = cuc.cust_id
join crm_contact_mail as cum
on cuc.id = cum.contact_id
join crm_mail_address as cma
on cum.mail_address_id = cma.id
and cma.address like '%m%'
)T
group by ttttt
查询到的记录数为8148条和下面的查询结果一样
select count(DISTINCT(cust.id)) as ttttt,u.user_id,u.display_name,cma.address
from crm_user as u
join crm_customer as cust
on u.user_id = cust.owner_id
and u.user_id = 10000
and cust.valid = 'Y'
join crm_cust_contact as cuc
on cust.id = cuc.cust_id
join crm_contact_mail as cum
on cuc.id = cum.contact_id
join crm_mail_address as cma
on cum.mail_address_id = cma.id
and cma.address like '%m%'
可是为什么直接用distinct(cust..id)却没有能去除cust.id重复的列,而这两种方法却可以呢?