如何将MS Access交叉表查询转换为MariaDB视图？

你的自定义MariaDB语句存在两个核心语法错误：

1. 子查询开头缺失SELECT关键字，导致整个查询结构非法
2. MariaDB的PIVOT语法与Access差异极大，无法直接沿用Access的写法，必须明确指定聚合规则和目标列值

以下提供两种可行的实现方案，适配不同的MariaDB版本需求：

方案1：使用MariaDB 10.5+原生PIVOT语法 #

MariaDB 10.5及以上版本支持原生PIVOT功能，语法需严格遵循「聚合函数+目标列+枚举值」的结构：

CREATE VIEW advdep_yearly_paymode AS
SELECT 
    `Year`,
    Total,
    -- 替换为你实际存在的Paymode枚举值，比如'现金'、'银行转账'等
    `现金`, `银行转账`, `微信支付`
FROM (
    SELECT 
        YEAR(advdep.AdvDate) AS `Year`,
        paymode.Paymode,
        SUM(advdep.Adv_Amt) AS pay_amt,
        -- 用窗口函数计算每年的总金额，对应Access中的Total字段
        SUM(SUM(advdep.Adv_Amt)) OVER (PARTITION BY YEAR(advdep.AdvDate)) AS Total
    FROM advdep 
    INNER JOIN paymode ON advdep.AdvMode = paymode.PayID
    GROUP BY YEAR(advdep.AdvDate), paymode.Paymode
) AS pivot_source
PIVOT (
    SUM(pay_amt)
    FOR Paymode IN (`现金`, `银行转账`, `微信支付`)
);

方案2：兼容性更强的条件聚合（适配所有MariaDB版本） #

如果你的MariaDB版本低于10.5，或需要更高兼容性，使用CASE条件聚合实现交叉表逻辑是更稳妥的选择：

CREATE VIEW advdep_yearly_paymode AS
SELECT 
    YEAR(advdep.AdvDate) AS `Year`,
    SUM(advdep.Adv_Amt) AS Total,
    -- 每个Paymode对应一个CASE分支，替换为实际的支付方式值
    SUM(CASE WHEN paymode.Paymode = '现金' THEN advdep.Adv_Amt ELSE 0 END) AS `现金`,
    SUM(CASE WHEN paymode.Paymode = '银行转账' THEN advdep.Adv_Amt ELSE 0 END) AS `银行转账`,
    SUM(CASE WHEN paymode.Paymode = '微信支付' THEN advdep.Adv_Amt ELSE 0 END) AS `微信支付`
    -- 若有更多支付方式，继续添加对应的CASE语句
FROM advdep 
INNER JOIN paymode ON advdep.AdvMode = paymode.PayID
GROUP BY YEAR(advdep.AdvDate);

补充说明 #

• Access中的Sum([advdep].[Adv_Amt])\1是取整数金额，若需要保留此逻辑，可将MariaDB中的SUM(advdep.Adv_Amt)替换为FLOOR(SUM(advdep.Adv_Amt))或CAST(SUM(advdep.Adv_Amt) AS UNSIGNED)
• 两种方案均需根据你实际的Paymode枚举值调整列名和条件，确保覆盖所有支付方式

内容的提问来源于stack exchange，提问作者Viney Kumar