Union All and Join in SQL

Question

我有两个表

t1: CustID    CustName
       1        aaa
       2        bbb
t2: CustID    Tax1    Tax2
       1      5     10
       2      4     8

我需要写一个查询，结果如下表

t3: CustID    CustName    TaxName    TaxValue
      1       aaa       Tax1       5
      1       aaa       Tax2       10
      2       bbb       Tax1       4
      2       bbb       Tax2       8

我能够联合所有

Select CustID,'Tax1' [TaxName], Tax1 [TaxValue]
from t2
union all
Select CustID,'Tax2' [TaxName], Tax2 [TaxValue]
from t2

但无法从t1加入此处的CustName

Answer 1

只需使用一个子查询：

select t2.custid, t1.custname, t2.taxname, t2.taxvalue
from ((Select CustID, 'Tax1' as [TaxName], Tax1 as [TaxValue] from t2
      ) union all
      (Select CustID, 'Tax2' as [TaxName], Tax2 as [TaxValue] from t2
      )
     ) t2 join
     t1
     on t1.custid = t2.custid;