主页 / dba / 问题 / 52109
Accepted
giannis christofakis
Asked: 2013-10-25 03:07:48 +0800 CST

优化具有相似字段的查询

  • 772

我有以下架构，表上的字段owner_id,billing_id,tech_id,admin_id指向carts表contacts。正如您从查询结果中看到的那样，我必须JOIN为每个字段。有没有办法避免？

在此处输入图像描述

SELECT c.domain, c.period, co.name AS owner, co1.name AS admin, co2.name AS tech, co3.name AS billing, h.nameserver
FROM carts c
LEFT JOIN contacts co ON c.owner_id = co.id
LEFT JOIN contacts co1 ON c.admin_id = co1.id
LEFT JOIN contacts co2 ON c.tech_id = co2.id
LEFT JOIN contacts co3 ON c.billing_id = co3.id
LEFT JOIN HOSTS h ON c.host_id = h.id
WHERE c.user_id =1

在此处输入图像描述

mysql

1 个回答

  1. Best Answer

    只需一个 join 和 pivoting 就可以获得相同的结果。

    SELECT c.domain, c.period, h.nameserver,
MAX(CASE WHEN co.id=c.owner_id THEN co.name END) AS owner, 
MAX(CASE WHEN co.id=c.admin_id THEN co.name END) AS admin,
MAX(CASE WHEN co.id=c.tech_id THEN co.name END) AS tech, 
MAX(CASE WHEN co.id=c.billing_id THEN co.name END)  AS billing
FROM carts c
LEFT JOIN contacts co ON co.id IN (c.owner_id , c.admin_id,c.tech_id,c.billing_id)

LEFT JOIN HOSTS h ON c.host_id = h.id
WHERE c.user_id =1
GROUP BY c.domain,c.period,h.nameserver

    但是，检查两个版本的执行计划，有时多个连接可能比这种方法更快。

    • 1

相关问题