Mysql 用其他列值替换列值

我有2张 table :

table: transaction:
====================
id  billed_date   amount
 1  2016-09-30      5
 2  2016-10-04      15
 3  2016-10-06      10

table: report_date
====================
transaction_id    report_date
      1            2016-10-01

我想要:

创建一个报告，汇总 2016 年 10 月的所有交易金额

基于报告日期，而非计费日期

未设置报告日期时，基于 billed_date

在上面的例子中，我希望结果是 30(不是 25)

然后我写:

首先:

SELECT
   sum(t.amount),
   CASE WHEN d.report_date IS NOT NULL THEN d.report_date ELSE t.billed_date END AS new_date
FROM
   transaction t LEFT JOIN report_date d ON t.id = d.transaction_id
WHERE new_date BETWEEN '2016-10-01' AND '2016-10-30'

第二:

SELECT sum(amount) FROM
 (SELECT t.amount,
    CASE WHEN d.report_date IS NOT NULL THEN d.report_date ELSE t.billed_date END AS date
    FROM transaction t LEFT JOIN report_date d ON t.id = d.transaction_id
 ) t
WHERE t.date BETWEEN '2016-10-01' AND '2016-10-30'

结果:

首先:

'where 子句'中的未知列'new_date'

如果我用 'date' 替换 'new_date':结果 = 25(排除 id=1)

第二:

result = 30 => 正确，但在我的情况下，当事务表有大约 30k 条记录时，过程太慢了。

任何人都可以帮助我吗？

最佳答案

首先 - 部分

CASE WHEN d.report_date IS NOT NULL THEN d.report_date ELSE t.billed_date END

可以写得更短

COALESCE(d.report_date, t.billed_date)

或作为

IFNULL(d.report_date, t.billed_date)

在您的第一个查询中，您在 WHERE 子句中使用了列别名，这是不允许的。您可以通过将别名后面的表达式移动到 WHERE 子句来修复它:

SELECT sum(t.amount)
FROM transaction t LEFT JOIN report_date d ON t.id = d.transaction_id
WHERE COALESCE(d.report_date, t.billed_date) BETWEEN '2016-10-01' AND '2016-10-30'

这与您自己的解决方案几乎相同。

您的第二个查询很慢，因为 MySQL 必须将子查询结果(30K 行)存储到临时表中。尝试优化它，您最终会得到与上述相同的解决方案。

但是，如果您在 transaction.billed_date 和 report_date.report_date 上有索引，则此查询仍然无法使用它们。为了使用索引，您可以将查询拆分为两部分:

带有报告的条目(将使用 report_date.report_date 索引):

SELECT sum(amount)
FROM transaction t JOIN report_date d ON id = transaction_id
WHERE d.report_date BETWEEN '2016-10-01' AND '2016-10-30'

没有报告的条目(将使用 transaction.billed_date 索引):

SELECT sum(amount)
FROM transaction t LEFT JOIN report_date d ON id = transaction_id
WHERE d.report_date IS NULL AND t.billed_dateBETWEEN '2016-10-01' AND '2016-10-30'

两个查询都可以使用索引。您只需要对结果求和，也可以结合两个查询来完成:

SELECT (
    SELECT sum(amount)
    FROM transaction t JOIN report_date d ON id = transaction_id
    WHERE d.report_date BETWEEN '2016-10-01' AND '2016-10-30'
) + (
    SELECT sum(amount)
    FROM transaction t LEFT JOIN report_date d ON id = transaction_id
    WHERE d.report_date IS NULL AND t.billed_dateBETWEEN '2016-10-01' AND '2016-10-30'
) AS sum_amount

关于Mysql 用其他列值替换列值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40055568/