时间戳

首页 > 解决方案 > 查看与 mariadb 上带有子查询的命令显示的结果不同

mysql - 查看与 mariadb 上带有子查询的命令显示的结果不同

问题描述

亲爱的，

我试图创建一个视图来总结一年中每个类别（行）和月份（列）的交易，如下所示：

SELECT counterpart.id_category as catid, category AS Category, (
    SELECT COALESCE(SUM(amount), 0)
    FROM transaction
    INNER JOIN counterpart
        ON transaction.id_counterpart = counterpart.id_counterpart
    WHERE YEAR(date) = YEAR(now())
        AND MONTH(date) = 1
        AND id_category = catid
) AS Jan, [...]
FROM transaction
INNER JOIN counterpart
    ON transaction.id_counterpart = counterpart.id_counterpart
INNER JOIN category
    ON counterpart.id_category = category.id_category
WHERE transaction.id_user = 2
    AND YEAR(date) = YEAR(now())
GROUP BY category

我不知道这是否是一个好方法，至少它可以作为单个命令而不是视图。

在视图中，WHERE id_category = catidfor 子查询无法正常工作。

谢谢你。

标签： mysqlviewsubquery

解决方案

我怀疑条件聚合会比许多子查询更合适和更快。

例如

SELECT counterpart.id_category as catid, category AS Category, 
sum(case when month(date) = 1 then 1 else 0 end) as Jan,
sum(case when month(date) = 2 then 1 else 0 end) as feb,
sum(case when month(date) = 3 then 1 else 0 end) as Mar
FROM transaction
INNER JOIN counterpart
    ON transaction.id_counterpart = counterpart.id_counterpart
INNER JOIN category
    ON counterpart.id_category = category.id_category
WHERE transaction.id_user = 2
    AND YEAR(date) = YEAR(now())
GROUP BY counterpart.id_category,category

推荐阅读