时间戳

首页 > 解决方案 > 将表中的列合并为单个表

sql-server - 将表中的列合并为单个表

问题描述

我有四个包含数据的表格，我想将每个表格的相应总列放在不同的列中。但是，我想在dealerId. 所以如果表1和表3中有一个dealerId相同的话，应该是单行。

表格1

dealerId | t1 Total Amount
---------+---------------
1        |  123
2        |  456

表 2

dealerId | t2 Total Amount
---------+----------------
3        | 111
4        | 222
5        | 333

表3

dealerId | t3 Total Amount
---------+----------------
1        | 555
3        | 565
6        | 888

表 4

dealerId | t4 Total Amount
---------+----------------
1        | 88
2        | 99
3        | 11

期望的结果

dealerId | t1Total Amount | t2Total Amount | t3 Total Amount | t4 Total Amount
---------+----------------+----------------+-----------------+-----------------
1        | 123            |  null          | 555             | 88
2        | 456            |  null          | null            | 99
3        | null           |  111           | 565             | 11
4        | null           |  222           | null            | null
5        | null           |  333           | null            | null
6        | null           |  null          | 888             | null

我基本上已经创建了视图（我不知道这是否是正确的术语）并尝试将它们全部合并，但这只会给我一个包含所有总数的列。

SELECT *
FROM
(
SELECT o.DealerId, Sum(oi.Amount) as T1_Total
FROM ....
) AS T1
UNION ALL
SELECT *
FROM
(
SELECT o.DealerId, Sum(oi.Amount) as T2_Total
FROM ....
) AS T2
UNION ALL
...
-- repeat for T3 and T4

标签： sql-servertsqlunion

解决方案

尝试做这样的事情：

  with all_dealer_ids AS (
    SELECT DISTINCT dealerId
    FROM Table1 
    UNION 
    SELECT DISTINCT dealerId
    FROM Table2
    UNION
    SELECT DISTINCT dealerId
    FROM Table3
    UNION
    SELECT DISTINCT dealerId
    FROM Table4 
  )

  SELECT adi.dealerId, SUM(t1.TotalAmount) As T1TotalAmount, SUM(t2.TotalAmount) As 
    T2TotalAmount, SUM(t3.TotalAmount) AS T3TotalAmount, SUM(t4.TotalAmount) AS T4TotalAmount
  FROM all_dealer_ids adi
  LEFT JOIN Table1 t1
    ON adi.dealerId = t1.dealerId
  LEFT JOIN Table2 t2
    ON adi.dealerId = t2.dealerId
  LEFT JOIN Table3 t3
    ON adi.dealerId = t3.dealerId
  LEFT JOIN Table4 t4
    ON adi.dealerId = t4.dealerId
  GROUP BY adi.dealerId
  ORDER BY adi.dealerId ASC

推荐阅读