mysql - 将 SQLite View 移植到 MySQL

Question

我必须将 SQLite 数据库“移植”到 MySQL。该数据库是通过带有 Connector/NET 库的 C# 应用程序设置的。

移植创建数据库表的方法很简单，但我陷入了一个 View 。

该 View 的 SQLite 代码运行良好，如下所示:

CREATE VIEW IF NOT EXISTS EXO_ResultsSummaries AS 
SELECT ParticipantBIB, 
       t.HeatIndex, 
       (CASE WHEN t.QualiGroup IS NULL THEN t.Finals ELSE t.QualiGroup END) AS HeatLevel, 
       PointsObstacle0 + PointsObstacle1 AS TotalScore, TimeObstacle0 + TimeObstacle1 AS TotalTime  
FROM (EXO_Results INNER JOIN Heats ON EXO_Results.HeatIndex = Heats.HeatIndex) t 
WHERE t.Completed = 1;

由于“IF NOT EXISTS”语句不适用于 MySQL，因此我将代码更改为以下内容:

CREATE OR REPLACE VIEW EXO_ResultsSummaries AS
SELECT ParticipantBIB, 
    t.HeatIndex, 
    (CASE WHEN t.QualiGroup IS NULL THEN t.Finals ELSE t.QualiGroup END) AS HeatLevel, 
    PointsObstacle0 + PointsObstacle1 AS TotalScore, TimeObstacle0 + TimeObstacle1 AS TotalTime  
FROM (EXO_Results INNER JOIN Heats ON EXO_Results.HeatIndex = Heats.HeatIndex) AS t 
WHERE t.Completed = 1;

但是，如果我尝试从应用程序(或工作台)创建 View ，则会收到此错误:

Error Code: 1064. You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS t  WHERE t.Completed = 1' at line 6

Answer 1

感谢 Shadow 的评论。相应地更改了我的代码，效果很好:

CREATE OR REPLACE VIEW EXO_ResultsSummaries AS SELECT ParticipantBIB, 
Heats.HeatIndex, 
(CASE WHEN Heats.QualiGroup IS NULL THEN Heats.Finals ELSE Heats.QualiGroup END) AS HeatLevel, 
PointsObstacle0 + PointsObstacle1 AS TotalScore, TimeObstacle0 + TimeObstacle1 AS TotalTime  
FROM EXO_Results INNER JOIN Heats ON EXO_Results.HeatIndex = Heats.HeatIndex  
WHERE Heats.Completed = 1;