mysql - 如何减少在数据库中选择数据的时间负载

Question

我想知道是否有其他方法可以减少在我的代码的数据库中获取数据时的时间负载:

SELECT cust.CUSTOMERNAME, cust.STDTERMS, (
        SELECT IFNULL(SUM(IFNULL(act.TOTALAMOUNT, 0) - IFNULL(act.DISCOUNTAMNT, 0)),0) 
        FROM 100_actual_transaction act 
        WHERE act.ATDATE BETWEEN CONCAT(intYear,'-',StartDate) AND CONCAT(intYear,'-',EndDate) AND act.SALESTYPE LIKE theSalesType AND act.CUSTOMER = cust.CUSTOMERNAME
        ) AS Amount, intYear AS intYear,
        (
        SELECT IFNULL(SUM(IFNULL(act.TOTALAMOUNT, 0) - IFNULL(act.DISCOUNTAMNT, 0)),0) 
        FROM 100_actual_transaction act 
        WHERE act.ATDATE BETWEEN CONCAT(intYear1,'-',StartDate) AND CONCAT(intYear1,'-',EndDate) AND act.SALESTYPE LIKE theSalesType  AND act.CUSTOMER = cust.CUSTOMERNAME
        ) AS Amount2, intYear1 AS intYear2,
        (
        SELECT IFNULL(SUM(IFNULL(act.TOTALAMOUNT, 0) - IFNULL(act.DISCOUNTAMNT, 0)),0) 
        FROM 100_actual_transaction act 
        WHERE act.ATDATE BETWEEN CONCAT(intYear2,'-',StartDate) AND CONCAT(intYear2,'-',EndDate) AND act.SALESTYPE LIKE theSalesType  AND act.CUSTOMER = cust.CUSTOMERNAME
        ) AS Amount3, intYear2 AS intYear3,
        (
        SELECT IFNULL(SUM(IFNULL(act.TOTALAMOUNT, 0) - IFNULL(act.DISCOUNTAMNT, 0)),0) 
        FROM 100_actual_transaction act 
        WHERE act.ATDATE BETWEEN CONCAT(intYear3,'-',StartDate) AND CONCAT(intYear3,'-',EndDate) AND act.SALESTYPE LIKE theSalesType  AND act.CUSTOMER = cust.CUSTOMERNAME
        ) AS Amount4, intYear3 AS intYear4,
        (
        SELECT IFNULL(SUM(IFNULL(act.TOTALAMOUNT, 0) - IFNULL(act.DISCOUNTAMNT, 0)),0) 
        FROM 100_actual_transaction act 
        WHERE act.ATDATE BETWEEN CONCAT(intYear4,'-',StartDate) AND CONCAT(intYear4,'-',EndDate) AND act.SALESTYPE LIKE theSalesType  AND act.CUSTOMER = cust.CUSTOMERNAME
        ) AS Amount5, intYear4 AS intYear5

        FROM 000_customer cust WHERE cust.CUSTOMERNAME = thecustomer;

在此代码中，它将选择每个选定 5 年的所有总金额看起来像这样，忽略这些值。

有人可以提出建议，让这段代码变得永恒加载吗？什么时候从数据库获取数据？我尝试了内部联接，但从数据库获取数据时的结果不正确。我无法使用在 where 子句中使用的变量:

from 000_customer cust where cust.customername = '%'

如果我在内部连接表内的选择查询中使用当前的客户名值。我会收到一条错误消息，指出 cust.customername 是未知列。

SELECT IFNULL(SUM(IFNULL(act.TOTALAMOUNT, 0) - IFNULL(act.DISCOUNTAMNT, 0)),0) AS Amount
        FROM 100_actual_transaction act 
        WHERE act.ATDATE BETWEEN CONCAT('2017','-','01-01') AND CONCAT('2017','-','12-30') AND act.CUSTOMER = cust.customername

这样查询就可以确定 cust.customername 值现在有什么。因为我实际上想显示每个客户每5年的总金额。不是总年数。只是每年。就像上面的例子一样。

我们非常感谢您的建议。提前致谢。

问题已解决:

更新的代码:

SELECT c.CUSTOMERNAME, c.STDTERMS,
        SUM(CASE WHEN a.ATDATE BETWEEN CONCAT('2017', '-', '01-01') AND CONCAT('2017', '-', '12-30')
        THEN COALESCE(a.TOTALAMOUNT, 0) - COALESCE(a.DISCOUNTAMNT, 0)
        ELSE 0
        END) AS Amount1,
        SUM(CASE WHEN a.ATDATE BETWEEN CONCAT('2016', '-', '01-01') AND CONCAT('2016', '-', '12-30')
        THEN COALESCE(a.TOTALAMOUNT, 0) - COALESCE(a.DISCOUNTAMNT, 0)
        ELSE 0
        END) AS Amount2,
        SUM(CASE WHEN a.ATDATE BETWEEN CONCAT('2015', '-', '01-01') AND CONCAT('2015', '-', '12-30')
        THEN COALESCE(a.TOTALAMOUNT, 0) - COALESCE(a.DISCOUNTAMNT, 0)
        ELSE 0
        END) AS Amount3,
        SUM(CASE WHEN a.ATDATE BETWEEN CONCAT('2014', '-', '01-01') AND CONCAT('2014', '-', '12-30')
        THEN COALESCE(a.TOTALAMOUNT, 0) - COALESCE(a.DISCOUNTAMNT, 0)
        ELSE 0
        END) AS Amount4,
        SUM(CASE WHEN a.ATDATE BETWEEN CONCAT('2013', '-', '01-01') AND CONCAT('2013', '-', '12-30')
        THEN COALESCE(a.TOTALAMOUNT, 0) - COALESCE(a.DISCOUNTAMNT, 0)
        ELSE 0
        END) AS Amount5
        FROM 000_customer c JOIN
        100_actual_transaction a
        ON a.CUSTOMER = c.CUSTOMERNAME AND 
        a.SALESTYPE LIKE '%'
        WHERE c.CUSTOMERNAME LIKE '%'
        GROUP BY c.CUSTOMERNAME, c.STDTERMS;

我刚刚输入了一个值，以便它澄清我的主要逻辑。谢谢你的帮助

Answer 1

使用条件聚合。我不确定你的逻辑到底是什么，但大概是这样的:

SELECT c.CUSTOMERNAME, c.STDTERMS,
       SUM(CASE WHEN a.ATDATE BETWEEN CONCAT(intYear, '-', StartDate) AND CONCAT(intYear, '-', EndDate)
                THEN COALESCE(a.TOTALAMOUNT, 0) - COALESCE(a.DISCOUNTAMNT, 0)
                ELSE 0
           END) as Amount
FROM 000_customer c JOIN
     100_actual_transaction a
     ON a.CUSTOMER = c.CUSTOMERNAME AND
        a.SALESTYPE LIKE theSalesType
WHERE c.CUSTOMERNAME = thecustomer
GROUP BY c.CUSTOMERNAME, c.STDTERMS;

我真的无法区分什么是列，什么是变量。我很确定这就是您正在寻找的逻辑。您需要为输出中想要的每个计算列重复 SUM(CASE)。