SQL Server SQL -如何连接多个表并使用SUM进行适当的添加？

我正在尝试将多个表（Microsoft SQL Server Management Studio v.18.9.1）联接在一起（基于ID），并使用求和聚合将其求和为一个新列。以下是我的表：
表格：加州州
| 加利福尼亚ID|名称名称名称|加利福尼亚州金额|
| - -|- -|- -|
| C1级|艾伦|一点|
| C1级|艾伦|一点|
| C1级|艾伦|一点|
| C1级|艾伦|二点|
| C2级|埃里克|二点|
| C2级|埃里克|二点|
| C2级|埃里克|三点整|
| C3级|雅内罗|三点整|
| C3级|雅内罗|三点整|
| C3级|雅内罗|五点整|
表格：德克萨斯州
| 德克萨斯州ID|名称名称名称|金额德克萨斯州|
| - -|- -|- -|
| 第二层|埃里克|二点零一分|
| 第二层|埃里克|二点零一分|
| 第二层|埃里克|二点零一分|
| 三号航站楼|扬|三点零一分|
| 三号航站楼|扬|三点零一分|
| 四层|利尔|四点零一分|
表格：佛罗里达州
| 佛罗里达ID|名称名称名称|佛罗里达州|
| - -|- -|- -|
| 五层|曼尼|十点整|
| 五层|曼尼|十点整|
| 第六层|尼娜|十一时|
| 三层|扬|100元|
| 四层|百合花|二十七点整|
表Junction 1，我创建该表是为了能够连接将来需要连接的任何表
所有相应的ID都匹配，这样我就有了加入的依据
| 加利福尼亚ID|德克萨斯州ID|佛罗里达ID|名称名称名称|
| - -|- -|- -|- -|
| C1级|第一层|F1代|艾伦B|
| C2级|第二层|二层|埃里克·D|
| C3级|三号航站楼|三层|杰内罗·T|
| 碳四|四层|四层|莱拉·E.|
| 五碳|五号航站楼|五层|马努埃洛河|
| 六碳|T6型|第六层|尼娜·H|
| C7级|七号航站楼|七层|佩里河|
| 八碳|八号航站楼|八层|拉莫斯T|
| 九碳|九号线|九层|斯凯湾|
| 碳十|T10型|十层|Trinity A|
当我执行下列查询时：

SELECT TOP 10 
    j.name, 
    COALESCE(SUM(t.amount1), 0) AS CaliforniaExpenses, 
    COALESCE(SUM(t.amount2), 0) AS TexasExpenses,    
    COALESCE(SUM(t.amount1), 0) + COALESCE(SUM(t.amount2), 0) AS TotalExpenses
FROM
    junction1 j
LEFT JOIN
    (SELECT 
         CaliforniaID, null AS TexasID, 
         AmountCalifornia AS amount1, null AS amount2 
     FROM
         test1 
     UNION ALL
     SELECT
         null, TexasID, null, AmountTexas 
     FROM
         test2) t ON t.CaliforniaID = j.CaliforniaID 
                     OR t.TexasID = j.TexasID 
GROUP BY
    j.name 
ORDER BY 
    CaliforniaExpenses DESC;

结果

| 名称名称名称|加利福尼亚费用|德克萨斯州费用|总费用|
| - -|- -|- -|- -|
| 杰内罗·T|十一时|六点零二分|十七点零二分|
| 埃里克·D|七点整|六点零三分|十三点零三分|
| 艾伦B|五点整|零点|五点整|
| Trinity A|零点|零点|零点|
| 斯凯湾|零点|零点|零点|
| 拉莫斯T|零点|零点|零点|
| 佩里河|零点|零点|零点|
| 尼娜·H|零点|零点|零点|
| 马努埃洛河|零点|零点|零点|
| 莱拉·E.|零点|三十三点零六分|三十三点零六分|
到目前为止，这是很棒的！有没有可能的方法潜入Florida列FloridaExpenses（就在TexasExpenses别名之后），并使其反映总额？
任何帮助都将不胜感激！

步骤1.将每个州的Expenses表聚合到ID级别。
步骤2.从Junction1到ID列上其他三个节点的左外联接

with 
cte_ca as (select CaliforniaID, sum(AmountCalifornia) as AmountCalifornia from California group by CaliforniaID),
cte_tx as (select TexasID, sum(AmountTexas) as AmountTexas from Texas group by TexasID),
cte_fl as (select FloridaID, sum(AmountFlorida) as AmountFlorida from Florida group by FloridaID)
select j.Name,
       coalesce(sum(ca.AmountCalifornia),0) as CaliforniaExpenses,
       coalesce(sum(tx.AmountTexas),0) as TexasExpenses,
       coalesce(sum(fl.AmountFlorida),0) as FloridaExpenses,
       coalesce(sum(ca.AmountCalifornia),0)  + coalesce(sum(tx.AmountTexas),0) + coalesce(sum(fl.AmountFlorida),0) as TotalExpenses
  from Junction1 j
  left
  join cte_ca ca on j.CaliforniaID = ca.CaliforniaID
  left
  join cte_tx tx on j.TexasID = tx.TexasID
  left
  join cte_fl fl on j.FloridaID = fl.FloridaID
 group by j.name
 order by j.name;

结果：

Name       |CaliforniaExpenses|TexasExpenses|FloridaExpenses|TotalExpenses|
-----------+------------------+-------------+---------------+-------------+
Alan B.    |            5.0000|       0.0000|         0.0000|       5.0000|
Eric D.    |            7.0000|       6.0300|         0.0000|      13.0300|
Janero T.  |           11.0000|       6.0200|       100.0000|     117.0200|
Lila E.    |            0.0000|       4.0100|        27.0000|      31.0100|
Manuello R.|            0.0000|       0.0000|        20.0000|      20.0000|
Nina H.    |            0.0000|       0.0000|        11.0000|      11.0000|
Perry R.   |            0.0000|       0.0000|         0.0000|       0.0000|
Ramos T.   |            0.0000|       0.0000|         0.0000|       0.0000|
Skye F.    |            0.0000|       0.0000|         0.0000|       0.0000|
Trinity A. |            0.0000|       0.0000|         0.0000|       0.0000|

db<>fiddle

SQL Server SQL -如何连接多个表并使用SUM进行适当的添加？

1条答案

相关问题

热门标签

最新问答