sql - 如何从具有相似列名的不同无关表中获取列？ | 如何从具有相似列名的不同无关表中获取列

我有3张桌子:

支付组:

PayGroupId   Name       Description    Code
1            US Weekly  US Weekly      USW
2            Can Weekly Canada Weekly  CANW
3            US Monthly US Monthly     USM
4            Can Monthly Can Monthly   CANM

付款方式:

PayTypeId   Name       Description    Code
1           Hourly     Hourly         H
2           Salary     Salaried       S

支付代码:

PayCodeId   Name       Description    Code
1           Regular    Regular        REG
2           PTO        PTO            PTO
3           Sick       Sick           SICK

我需要以下格式的报告:

PayGroup    PayType    PayCode
US Weekly   Hourly     Regular
Can Weekly  Salary     PTO
US Monthly             Sick

我们可以做到吗？

最佳答案

我怀疑这会为您带来想要的结果，但似乎是一个奇怪的要求:

WITH PG AS(
    SELECT [Name],
           ROW_NUMBER() OVER (ORDER BY PayGroupID ASC) AS RN
    FROM PayGroup),
PT AS(
    SELECT [Name],
           ROW_NUMBER() OVER (ORDER BY PayTypeID ASC) AS RN
    FROM PayGroup),
PC AS(
    SELECT [Name],
           ROW_NUMBER() OVER (ORDER BY PayCodeID ASC) AS RN
    FROM PayCode)
SELECT PG.[Name] AS PayGroup,
       PT.[Name] AS PayType,
       PC.[Name] AS PayCode
FROM PG
     FULL OUTER JOIN PT ON PG.RN = PT.RN
     FULL OUTER JOIN PC ON PG.RN = PC.RN
                        OR PT.RN = PC.RN;

关于sql - 如何从具有相似列名的不同无关表中获取列？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55417134/