tsql - 我可以在 Microsoft SQL Server 中将 PIVOT 与内部联接结合使用吗？

我有以下 SQL 查询:

SELECT CountryID, [10201] AS CountryGDPPerCapita, [10677] AS LifeExpTotal
FROM
(
    SELECT CountryID,FieldID,numeric
    FROM globaledge.dbo.DIBS_Data
    WHERE CountryID IN (3,5)
    AND FieldID IN (10201,10677)
    AND year = 2002
)  SourceTable
PIVOT
(
    MAX(numeric)
    FOR FieldID IN ([10201],[10677])
) AS PivotTable
ORDER BY PivotTable.CountryID

这将返回如下所示的内容:

CountryID CountryGDPPerCapita LifeExpTot​​al

3 35985.78 77.24

5 9147.7 74.54

然后我有另一个查询如下:

SELECT CountryName, CountryGDP, CountryGDPGrowth
FROM globaledge.dbo.Country_Statistics
WHERE CountryID IN (3,5)
AND year=2002
Order By CountryName

产生以下结果:

CountryName CountryGDP CountryGDPGrowth

墨西哥 1567000000000000 1.3

美国 14440000000000000 0.4

另请注意，我在两个表中都有 CountryID，它们指的是同一个国家。我想要的是创建一个 SQL 查询，也许使用内部联接，它将返回以下内容:

CountryName CountryGDP CountryGDPGrowth CountryGDPPerCapita LifeExpTot​​al

墨西哥 156700000000000000 1.3 35985.78 77.24

美国 144400000000000000 0.4 9147.7 74.54

谁能帮我做这个查询？或者告诉我是否可能？

最佳答案

像这样的事情会起作用:

SELECT
  a.CountryID, a.CountryName, a.CountryGDP, a.CountryGDPGrowth
, b.CountryGDPPerCapita, b.LifeExpTotal
FROM
(
    SELECT CountryID, CountryName, CountryGDP, CountryGDPGrowth
    FROM globaledge.dbo.Country_Statistics
    WHERE CountryID IN (3,5)
    AND year=2002
) AS a
JOIN
(
    SELECT CountryID, [10201] AS CountryGDPPerCapita, [10677] AS LifeExpTotal
    FROM
    (
        SELECT CountryID,FieldID,numeric
        FROM globaledge.dbo.DIBS_Data
        WHERE CountryID IN (3,5)
        AND FieldID IN (10201,10677)
        AND year = 2002
    )  SourceTable
    PIVOT
    (
        MAX(numeric)
        FOR FieldID IN ([10201],[10677])
    ) AS PivotTable
) AS b ON a.CountryID = b.CountryID
Order By a.CountryName

关于tsql - 我可以在 Microsoft SQL Server 中将 PIVOT 与内部联接结合使用吗？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2298481/