如何将SQL查询从MS Access转换为Mariadb SQL

svmlkihl 于 2023-11-19 发布在其他

关注(0)|答案(1)|浏览(161)

这是一个使用两个表的查询，一个是advdep，另一个是paymode
advdep表：
AdvDate AdvMode（高级模式）（高级_amt）
Paymode表：PayID Paymode
我在MS Access中使用交叉表查询，效果很好（需要很多时间！）

TRANSFORM Sum(advdep.Adv_Amt) AS Totals
       SELECT Year([AdvDate]) AS [Year], Sum([advdep].[Adv_Amt])\1 AS Total
       FROM advdep INNER JOIN paymode ON advdep.AdvMode = paymode.PayID
GROUP BY Year([AdvDate])
PIVOT paymode.Paymode;

字符串
我需要把这个转换成mariadb视图

SELECT *
    FROM
   (
   Sum(advdep.Adv_Amt) AS Totals,
   Year([AdvDate]) AS [Year], Sum([advdep].[Adv_Amt]) AS Total
   FROM advdep INNER JOIN paymode ON advdep.AdvMode = paymode.PayID
   ) t
PIVOT paymode.Paymode;

型
它显示查询语法错误

mariadb

来源：https://stackoverflow.com/questions/77425425/how-can-i-convert-sql-query-from-ms-access-to-mariadb-sql

1条答案

按热度按时间

6jygbczu1#

SELECT YEAR(advdate) AS Year,
      SUM(adv_amt) AS Total,
      sum(CASE WHEN advmode='1' THEN adv_amt END) `CSH`,
      sum(CASE WHEN advmode='2' THEN adv_amt END) `CHQ`,
      sum(CASE WHEN advmode='3' THEN adv_amt END) `CRD`,
      sum(CASE WHEN advmode='4' THEN adv_amt END) `NEFT`,
      sum(CASE WHEN advmode='7' THEN adv_amt END) `UPI`
      
  FROM advdep
 GROUP BY YEAR(advdate);

字符串

赞(0）回复(0）举报 2023-11-19

我来回答

如何将SQL查询从MS Access转换为Mariadb SQL

1条答案

相关问题

热门标签

最新问答