将MySQL SQL转换为在MS-Access数据库中工作

Trying to convert this working SQL from MySql database to work on a MS Access database:

尝试将此工作SQL从MySql数据库转换为在MS Access数据库上工作：

    SELECT u.LastName AS LAST, u.FirstName AS FIRST,
    MAX(IF(`e.ClassName`='MDC (Intro)', DateCompleted, NULL)) AS 'MDC', 
    MAX(IF(`e.ClassName`='800 MHz Radio (Intro)', DateCompleted, NULL)) AS 'RADIO',
    MAX(IF(`e.ClassName`='ePCR (Intro)', DateCompleted, NULL)) AS 'ePCR',
    MAX(IF(`e.ClassName`='Firehouse (Incident)', DateCompleted, NULL)) AS 'Firehouse'
    FROM EnrollmentsTbl e INNER JOIN UsersDataTbl u ON e.UserName = u.UserName
    GROUP BY e.UserName 
    WHERE u.LastName LIKE 'Bar%' 
    ORDER BY u.LastName

2 个解决方案

#1

A few items of syntax requires conversion:

一些语法项需要转换：

A very important item and one unfortunately MySQL users tend to abuse (which run with only full group by mode off). In SQL, GROUP BY must include ALL non-aggregated columns. So add FirstName and LastName to the grouping.
一个非常重要的项目和一个不幸的MySQL用户倾向于滥用（通过模式关闭只运行完整组）。在SQL中，GROUP BY必须包含所有非聚合列。因此，将FirstName和LastName添加到分组中。
MS Access SQL does not use IF for conditional expressions but IIF().
MS Access SQL不对条件表达式使用IF，而使用IIF（）。
While Access does use backticks and square brackets, it can only be used to enclose column names and/or table names not both together. Come to think of it MySQL allows backticks around column and/or table names but not both in same enclosure pair (just checked in MySQL 5.5).
虽然Access确实使用了反引号和方括号，但它只能用于将列名和/或表名括在一起。想一想MySQL允许在列和/或表名称周围进行反引号，但不能在同一个机箱对中进行反引号（仅在MySQL 5.5中检查）。
Access does not identify column aliases with any single or double quote which the latter is the ANSI standard. Interestingly, you can include quotes but they will show up literally in column name. MySQL adheres to ANSI double quote for object identifiers with ANSI-Quotes sql mode on.
Access不会识别具有任何单引号或双引号的列别名，后者是ANSI标准。有趣的是，您可以包含引号，但它们将以列名的形式显示。 MySQL遵循ANSI-Quotes sql模式的对象标识符的ANSI双引号。
Finally, Access via ODBC does use % wildcard for LIKE evaluation but Access via the GUI .exe program uses * by default. But its ALIKE operator is valid in both setup types.
最后，通过ODBC访问确实使用％通配符进行LIKE评估，但通过GUI .exe程序访问默认使用*。但它的ALIKE运算符在两种设置类型中都有效。

Consider the following SQL adjustment:

请考虑以下SQL调整：

SELECT u.LastName AS `LAST`, u.FirstName AS `FIRST`,
       MAX(IIF(e.`ClassName`='MDC (Intro)', DateCompleted, NULL)) AS `MDC`, 
       MAX(IIF(e.`ClassName`='800 MHz Radio (Intro)', DateCompleted, NULL)) AS `RADIO`,
       MAX(IIF(e.`ClassName`='ePCR (Intro)', DateCompleted, NULL)) AS `ePCR`,
       MAX(IIF(e.`ClassName`='Firehouse (Incident)', DateCompleted, NULL)) AS `Firehouse`
FROM EnrollmentsTbl e 
INNER JOIN UsersDataTbl u ON e.UserName = u.UserName
GROUP BY u.LastName, u.FirstName, e.UserName 
WHERE u.LastName ALIKE 'Bar%' 
ORDER BY u.LastName

#2

I'm not sure if its aligned exactly as I want it (need first/last in the first column, and the GROUP BY was the main problem, but this returns results from Access:

我不确定它是否与我想要的完全一致（在第一列中需要first / last，而GROUP BY是主要问题，但这会返回Access的结果：

SELECT u.LastName AS [LAST], 
Max(IIf([e.ClassName]='MDC (Intro)',Format([DateCompleted],'mm/dd/yyyy'),Null)) AS [MDC], 
Max(IIf([e.ClassName]='800 MHz Radio (Intro)',Format([DateCompleted],'mm/dd/yyyy'),Null)) AS [RADIO], 
Max(IIf([e.ClassName]='ePCR (Intro)',Format([DateCompleted],'mm/dd/yyyy'),Null)) AS [ePCR], 
Max(IIf([e.ClassName]='Firehouse (Incident)',Format([DateCompleted],'mm/dd/yyyy'),Null)) AS [Firehouse]
FROM EnrollmentsTbl e 
INNER JOIN UsersDataTbl u ON e.UserName = u.UserName
GROUP BY u.LastName, u.FirstName, e.UserName
WHERE u.UserName LIKE 'bar%' 
ORDER BY u.LastName;

#1