时间戳

首页 > 解决方案 > 无法使用 mysql 3 joins 查询获取完整数据

mysql - 无法使用 mysql 3 joins 查询获取完整数据

问题描述

我在域表中有 1500 条记录，但使用此查询只能获得 1215 条记录。如何修改此查询以提供所需的结果和更好的性能

SELECT 
  d.id, d.domain_name, d.action, d.comment, d.agent_email, 
  d.assigned_date, d.added_date, dd.registered_on, dd.expiry_date,
  dd.updated_date, dd.acquire_price, dd.acquire_date, dd.email,
  dd.effective_price, dd.registrar, dd.status, dd.servers, 
  count(l.lead_domain), d.domainer_email, d.current_status, 
  d.undeveloped, d.sedo, d.afternic, d.flippa, d.uniregistry, 
  d.go_daddy, d.domr, d.minimum_offer, d.buy_it_now_price, 
  d.way_to_find_leads, dd.tlds_taken 
FROM domains d 
right join domains_data dd on d.domain_name=dd.domain_name 
left outer join lead_domains l on d.domain_name=l.domain_name 
  and d.domainer_email=l.domainer_email 
group by d.domain_name 
having d.action='all' and d.domainer_email='abc@gmail.com' 
order by d.added_date desc;

标签： mysqlsql

解决方案

您的结果取决于domains_data表格。

由于您正确加入domains，domains_data因此将采用其中的条目domains_data。如果您希望domains考虑 的条目，请使用如下所示的左连接。

SELECT d.id, d.domain_name, d.action, d.comment, d.agent_email, d.assigned_date, d.added_date, dd.registered_on, dd.expiry_date, dd.updated_date, dd.acquire_price, dd.acquire_date, dd.email, dd.effective_price, dd.registrar, dd.status, dd.servers, COUNT(l.lead_domain), d.domainer_email, d.current_status, d.undeveloped, d.sedo, d.afternic, d.flippa, d.uniregistry, d.go_daddy, d.domr, d.minimum_offer, d.buy_it_now_price, d.way_to_find_leads, dd.tlds_taken
FROM domains d
LEFT JOIN domains_data dd ON d.domain_name=dd.domain_name
LEFT OUTER JOIN lead_domains l ON d.domain_name=l.domain_name AND d.domainer_email=l.domainer_email
GROUP BY d.domain_name
HAVING d.action='all' AND d.domainer_email='abc@gmail.com'
ORDER BY d.added_date DESC;

如果您仍然无法获得所需的结果，请检查having条件。

推荐阅读