如何在 sqlite 查询的输出中使用相同名称的列作为我在 join 的 ON 子句中进行比较的值？

Question

我原来的问题

当我在 SQLite 中执行以下查询时，出现此错误：

查询错误：滥用聚合：sum() 无法执行语句

当我将“Loan”列的名称更改为类似“loan_amount”的名称时，错误消失了，我的查询工作正常。为什么“贷款”出现问题？

select
    t.*

    , coalesce(sum(ded0.after_tax_ded_amt), 0) as "Loan"

    , coalesce(sum(ded1.after_tax_ded_amt), 0) as ee_advance_amount

from totals t

  left join totals as ded0
    on t.ee_ssn = ded0.ee_ssn
    and t.deduction_code = "Loan"
    and ded0.deduction_code = "Loan"

  left join totals as ded1
    on t.ee_ssn = ded1.ee_ssn
    and t.deduction_code = "EE Advance"
    and ded1.deduction_code = "EE Advance"

group by t.ee_ssn;

---

中后启示

我很确定我明白了为什么会出现错误，是因为我在连接的子句中与“Loan”进行比较吗？

如果是这样，我如何在查询输出中仍然使用“Loan”一词作为我的列名称？

Answer 1

我猜你真正的问题是引用滥用。 SQL 中的单引号用于引用字符串文字，双引号用于引用需要区分大小写或包含奇数字符的列名和表名。 SQLite 对奇怪的语法相当宽容，因此它可能会猜测 "Loan" 的含义，但猜测不正确。试试这个：

select
    t.*
    , coalesce(sum(ded0.after_tax_ded_amt), 0) as "Loan"
    , coalesce(sum(ded1.after_tax_ded_amt), 0) as ee_advance_amount
from totals t
  left join totals as ded0
    on t.ee_ssn = ded0.ee_ssn
    and t.deduction_code = 'Loan'
    and ded0.deduction_code = 'Loan'
  left join totals as ded1
    on t.ee_ssn = ded1.ee_ssn
    and t.deduction_code = 'EE Advance'
    and ded1.deduction_code = 'EE Advance'
group by t.ee_ssn;