代码之家 › 专栏 › 技术社区 › YvetteLee

MS Access SQL到MySQL-转换/透视

mysql

-2

YvetteLee · 技术社区 · 6 年前

我正在测试我的MS Access数据库到MySQL。我是MySQL的新手。我有一个在Access中运行良好的SQL。我想在MySQL中转换并测试它。

TRANSFORM IIf(Sum([price]) Is Null,0,Sum([price])) AS SumPrice
SELECT Format([AppointmentDate],"yyyy") AS [Year], IIf(Sum([SumPrice]) Is Null,0,Sum([SumPrice])) AS TotalPricePerYear
FROM tblAppointment
GROUP BY Format([AppointmentDate],"yyyy")
ORDER BY Format([AppointmentDate],"yyyy") DESC 
PIVOT Month([AppointmentDate]) In (1,2,3,4,5,6,7,8,9,10,11,12);

提前谢谢。

1 回复 | 直到 6 年前

YvetteLee 6 年前

SELECT year
     , SUM(CASE WHEN month =  1 THEN price END) jan_revenue
     , SUM(CASE WHEN month =  2 THEN price END) feb_revenue
     ...
     , SUM(CASE WHEN month = 12 THEN price END) dec_revenue
FROM (SELECT tblAppointment.*
           , EXTRACT(YEAR  FROM AppointmentDate) year
           , EXTRACT(MONTH FROM AppointmentDate) month
       FROM tblAppointment 
     ) tblAppointment
GROUP BY year

推荐文章

Badal Solanki · 如何在MySQL中对字符串进行算术运算?

1 年前

Saleh Mehdiyev · 我想重构我的Laravel代码以遵守SOLID原则

1 年前

hello_programmers · Mysql从其他表输出一列

1 年前

Community wiki · 这个MySQL语句出了什么问题?

1 年前

Community wiki · 优化从同一表中提取的多列的查询

1 年前