视图显示的结果与 mariadb 上带有子查询的命令不同

Question

Dears，

我试图创建一个视图，将一年中每个类别(行(和月份(列(的事务汇总为：

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 = catid无法正常工作。

谢谢。

Answer 1

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

例如

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