mysql - 转向 SQL Access

Question

我有一张这样的 table

Index  |  FeatureType  |  FeatureExists
1      |  BR           |  0
1      |  EI           |  NULL
1      |  RD           |  NULL
1      |  SEI          |  0
1      |  SNI          |  NULL
1      |  SSI          |  0

我想将其转换如下:

Index | BR | EI   | RD   | SEI | SNI  | SSI
1     | 0  | NULL | NULL | 0   | NULL | 0

这是我在 Microsoft Access 应用程序中使用的代码:

TRANSFORM First(QueryFeatureLicenses.FeatureExists) AS PremierDeFeatureExists
SELECT QueryFeatureLicenses.Index AS [Index]
FROM QueryFeatureLicenses
GROUP BY QueryFeatureLicenses.Index
PIVOT QueryFeatureLicenses.FeatureType;

它就像一个魅力。

但是我应该如何用 SQL 语法转换这个查询呢？到目前为止，我已经做到了:

SELECT BR, EI, RD, SEI, SNI, SSI
FROM
(
    SELECT FeatureType, FeatureExists
    FROM QueryFeatureLicenses
) AS d
PIVOT
(
    max(FeatureExists)
    FOR FeatureType IN (BR,EI,RD, SEI,SNI,SSI)
) AS piv;

但我总是收到以下消息:

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 'PIVOT ( max(FeatureExists) FOR FeatureType IN (BR,EI,RD, SEI,SN' at line 7

有什么想法吗？

Answer 1

PIVOT 在 MySQL 中不可用。作为替代方案，您可以使用条件聚合来实现此目的:

SELECT MAX(CASE WHEN FeatureType = 'BR' THEN FeatureExists END) AS BR
       MAX(CASE WHEN FeatureType = 'EI' THEN FeatureExists END) AS EI,
       MAX(CASE WHEN FeatureType = 'RD' THEN FeatureExists END) AS RD,
       MAX(CASE WHEN FeatureType = 'SEI' THEN FeatureExists END) AS SEI,
       MAX(CASE WHEN FeatureType = 'SNI' THEN FeatureExists END) AS SNI,
       MAX(CASE WHEN FeatureType = 'SSI' THEN FeatureExists END) AS SSI
FROM QueryFeatureLicenses
GROUP BY Index