mysql - SQL查询以识别不相等的值并以转置格式显示

我们有一个CountryDtls表，其中包含国家名称，执行的活动和年份。
我只需要识别艺术和学院之间年份值不同的那些行，并以下面给出的不同格式显示

原始清单：

年|国家名称|活动|
------ | ------------ | ------------ |
1987 |印度|艺术|
1987 |印度|科学|
1876 |印度|学院|
1876 | Aus |艺术|
1836 | Aus |科学|
1876 | Aus |学院|
1976年|美国|艺术|
1976年|美国|科学|
1976年|美国|学院|


预期结果：

CountryName |艺术|学院|
------------ | ----- | -------- |
印度| 1987 | 1876 |


我可以使用以下查询获得结果。有没有更好的方法来达到预期的效果？

选择c.CountryName，（从CountryDtls选择Year，其中environment =“ Arts”和c.CountryName = CountryName）
，（从CountryDtls的环境中选择Year，其中environment =“ Academy”和c.CountryName = CountryName）从CountryDtls选为Academy，而c where environment =“ Arts” AND environment =“ Arts” AND（CountryName）IN（从CountryDtls中选择CountryName，其中environment =“学院”）和（年份）不在（从CountryDtls中选择年份，其中environment =“ Academy”）

最佳答案

我认为自我联接（使用子查询）提供了最清晰的解决方案：

SELECT
    Arts.CountryName,
    Arts.Yr AS Arts,
    Academy.Yr AS Academy
FROM
    (
        SELECT Yr, CountryName, Activity
        FROM CountryDtls WHERE Activity = 'Arts'
    ) AS Arts
    INNER JOIN
    (
        SELECT Yr, CountryName, Activity
        FROM CountryDtls WHERE Activity = 'Academy'
    ) AS Academy ON
        Academy.CountryName = Arts.CountryName
WHERE Arts.Yr <> Academy.Yr

如果您的MySQL≥8.0，则可以使用CTE使其更具可读性：

WITH
    Arts AS
    (
        SELECT yr, CountryName, Activity
        FROM CountryDtls WHERE Activity = 'Arts'
    ),
    Academy AS
    (
        SELECT yr, CountryName, Activity
        FROM CountryDtls WHERE Activity = 'Academy'
    )
SELECT
    Arts.CountryName,
    Arts.Yr AS Arts,
    Academy.Yr AS Academy
FROM Arts INNER JOIN Academy ON Academy.CountryName = Arts.CountryName
WHERE Arts.Yr <> Academy.Yr