我有一个执行缓慢的查询。我知道使用依赖子查询是不好的,但是我想不出其他方法来获取我想要的数据。
基本上,我想标记过去6个月内至少有50张发票,但本月没有发票的客户。
这就是我目前所拥有的:

select
    Customer.name,
    Customer.id,
    Customer.latitude,
    Customer.longitude
from
    Customer
where
    EXISTS (
        SELECT
            *
        FROM
            Invoice_Header
        WHERE
            Invoice_Header.inv_date BETWEEN '2011-03-02' AND '2011-10-02'
        AND
            Invoice_Header.account_number = Customer.account_number
        HAVING COUNT(invoice_num) > 50
    )
    AND NOT EXISTS (
        SELECT *
        FROM
            Invoice_Header
        WHERE
            InvHead.inv_date > '2011-10-02'
        AND
            InvHead.account_number = Customer.account_number
    )
Group by name;

Customer表有大约12k条记录,Invoice_Header有大约2条ill记录。
我有库存日期、帐号的索引(在两个表中)。
任何关于如何加快这一进程的建议都将不胜感激。

最佳答案

我建议:

SELECT
    c.name,
    c.id,
    c.latitude,
    c.longitude
FROM
    Customer AS c
    INNER JOIN (
        SELECT account_number, count(*) AS invoice_count
        FROM Invoice_Header
        WHERE inv_date >= '2011-03-02' AND inv_date <= '2011-10-02'
        GROUP BY account_number
    ) AS lsm
        ON c.account_number = lsm.account_number
    LEFT JOIN (
        SELECT account_number, count(*) AS invoice_count
        FROM Invoice_Header
        WHERE inv_date > '2011-10-02'
        GROUP BY account_number
    ) AS lm
        ON c.account_number = lm.account_number
    WHERE
        lsm.invoice_count >= 50
        AND IFNULL(lm.invoice_count, 0) = 0

关于mysql - 需要MySQL查询优化,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/7987135/

10-14 12:42
查看更多