mysql - 如何在 MySQL SUM 子查询中创建可用的别名
问题描述
在以下 MySQL 查询中,我能够生成 Sum 值,但无法分配可用于在 PHP 页面中回显的值。
SELECT
invoices.InvoiceNo AS Invoice,
invoices.invDate AS DATE,
invoices.invValue AS Amount,
(
SELECT SUM(invoices.invValue) AS GrandTotal
FROM invoices
WHERE invoices.fKey = 186 AND inv_openClosed = 0
)
FROM invoices
WHERE invoices.fKey = 186 AND inv_openClosed = 0
ORDER BY Invoice DESC
测试显示结果,但“GrandTotal”的别名不起作用。并且 MySQL 没有错误来寻求解决方案。
解决方案
别名需要在外部查询而不是内部查询中:
SELECT i.InvoiceNo AS Invoice,
i.invDate AS DATE,
i.invValue AS Amount,
(SELECT SUM(i2.invValue)
FROM invoices i2
WHERE i2.fKey = 186 AND i2.inv_openClosed = 0
) AS GrandTotal
FROM invoices i
WHERE i.fKey = 186 AND i.inv_openClosed = 0
ORDER BY i.Invoice DESC;
您会注意到我还包括了表别名和限定列名。您也可以将其写为相关子查询,因此您不需要重复常量:
(SELECT SUM(i2.invValue)
FROM invoices i2
WHERE i2.fKey = i.fKey AND i2.inv_openClosed = i.inv_openClosed
) AS GrandTotal
在 MySQL 8+ 中,您可以使用窗口函数:
SELECT i.InvoiceNo AS Invoice,
i.invDate AS DATE,
i.invValue AS Amount,
SUM(i.invValue) OVER (PARTITION BY fKey, inv_openClosed) as GrandTotal
FROM invoices i
WHERE i.fKey = 186 AND i.inv_openClosed = 0
ORDER BY i.Invoice DESC;
推荐阅读
- python - 在 Selenium/Python 中出错 - chromedriver 可执行文件需要在 PATH 中
- excel - 将对应的值从一列复制到另一列
- javafx - 如何设置 javafx 列的固定大小?
- git - 将文件从 git 存储库移动到工作文件夹?
- javascript - 使用Highchart而不使用JQuery时如何在外部触发图例点击事件
- python - 如何通过合并公共列并保留其余列来合并两个大熊猫数据框,无需键
- python-3.x - 如何使用 MSB/LSB 方法分解图像?
- php - PHP Object to JSON:如何创建具有多个递归子级的类?
- go - 计算线性回归线的角度
- c++ - 使用 std::unique_ptr 提升图捆绑属性