如何合并所有行，然后合并sqlserver中所有列的总和 [英] How to merge all rows and then sum of all columns in sqlserver

问题描述

我有两张桌子 Fee_Assign_to_Students 和 Fee_Assign_Waiver_to_Students ，两张桌子都有像

这样的栏目

 S_Adm_No | FHeadId |四月|五月|君|七月|八月|九月|十月| 11月| 12月| Jan |二月| Mar 

我希望合并所有行，然后分别合并所有月份列。



我的代码如下：



Fee_Assign_to_Students 有价值

 S_Adm_No | FHeadId |四月|五月|君|七月|八月|九月|十月| 11月| 12月| Jan |二月| Mar 
 1001 | 1 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 
 1001 | 2 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 
 1001 | 3 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 

Fee_Assign_Waiver_to_Students 的值为< br $> b $ b

 S_Adm_No | FHeadId |四月|五月|君|七月|八月|九月|十月| 11月| 12月| Jan |二月| Mar 
 1001 | 1 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 
 1001 | 2 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 
 1001 | 3 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 

从 Fee_Assign_to_Students 减去sql querry之后的
。它就像

 S_Adm_No | FHeadId |四月|五月|君|七月|八月|九月|十月| 11月| 12月| Jan |二月| Mar 
 1001 | 1 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 
 1001 | 2 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 
 1001 | 3 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 | 50 

但我想要像

 S_Adm_No |四月|五月|君|七月|八月|九月|十月| 11月| 12月| Jan |二月| Mar 
 1001 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 

请任何人帮助我



我的代码在下面..



我尝试了什么：

  SELECT  FA.S_Adm_No，FA。[FHeadId]，
 FA.APR  -  ISNULL（FET.APR， 0 ）  AS  APR，
 FA.MAY  -  ISNULL（FET.MAY， 0 ） AS  MAY，
 FA.JUN  -  ISNULL（FET.JUN， 0 ） AS  JUN，
 FA.JUL  -  ISNULL（FET.JUL， 0 ） AS  JUL，
 FA.AUG  -  ISNULL（FET.AUG， 0 ） AS  AUG，
 FA.Sept  -  ISNULL（FET.SEP， 0 ） AS  SEP，
 FA.OCT  -  ISNULL（FET.OCT， 0 ） AS  OCT，
 FA.NOV  - ISNULL（FET.NOV， 0 ） AS  NOV，
 FA.DEC  -  ISNULL（FET .DEC， 0 ） AS  DEC，
 FA.JAN  -  ISNULL（FET.JAN，  0 ） AS  JAN，
 FA.FEB  -  ISNULL（FET.FEB， 0 ） AS  FEB，
 FA.MAR  -  ISNULL（FET.MAR， 0 ） AS  MAR 
  FROM  Fee_Assign_to_Students FA  LEFT   JOIN  
（ SELECT  FE.S_Adm_No ，FE。[FHeadId]，
 SUM（FE.E_Apr） AS  APR，
 SUM（FE.E_MAY） AS  MAY，
 SUM（FE.E_JUN） AS  JUN，
 SUM（FE.E_JUL） AS  JUL，
 SUM（FE.E_AUG） AS  AUG，
 SUM（FE.E_SEP ） AS  SEP，
 SUM（FE.E_OCT） AS  OCT，
 SUM （FE.E_NOV） AS  NOV，
 SUM（FE.E_DEC） AS  DEC，
 SUM（FE.E_JAN） AS  JAN，
 SUM（FE.E_FEB） AS  FEB，
 SUM（FE.E_MAR） AS  MAR 
  FROM  Fee_Assign_Waiver_to_Students FE 
  GROUP   BY  FE.S_Adm_No，FE.FHeadId）
  AS  FET  ON （FA.S_Adm_No = FET.S_Adm_No） AND （FA.FHeadId = FET.FHeadId） WHERE  FA.S_Adm_No = '  1001' 

解决方案

我已在这里回答了你的类似问题：如何在sqlserver中创建数据透视表 [ ^ ]



第一眼看错了方法！两个表的结构似乎是相同的。为什么？



由于联接类型，结果集可能会有所不同。请阅读： SQL连接的可视化表示 [ ^ ]。



我用 UNION ALL ：

  SELECT  T.S_Adm_No，SUM（T.Apr） AS  4月，SUM（T.May）  AS  May，SUM（T.Jun） As  Jun，SUM（T.Jul） As  7月，SUM（T.Aug） AS  8月，SUM（T.Sep）作为 9月，
 SUM（T.Oct） AS  10月，SUM（T.Nov） AS  11月，SUM（T.Dec） AS  Dec，SUM（T.Jan） AS  Jan，SUM（T.Feb） AS  2月，SUM（T.Mar） AS  3月
  FROM （
  SELECT  FAS。* 
  FROM  Fee_Assign_to_Students  AS  FAS 
  UNION   ALL  
  SELECT 一汽。 * 
  FROM  Fee_Assign_Waiver_to_Students  AS  FAW 
） AS  T 
  GROUP   BY  T.S_Adm_No 

I have two tables Fee_Assign_to_Students and Fee_Assign_Waiver_to_Students and both tables have columns like

S_Adm_No | FHeadId | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar

I want merge all rows and then sum of all month columns separately.

My code is mentioned below

Fee_Assign_to_Students has value as

S_Adm_No | FHeadId | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar
1001     |    1    | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100
1001     |    2    | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100
1001     |    3    | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100

Fee_Assign_Waiver_to_Students has value as

S_Adm_No | FHeadId | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar
1001     |    1    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50
1001     |    2    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50
1001     |    3    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50

after subtraction from Fee_Assign_to_Students through sql querry. It comes like

S_Adm_No | FHeadId | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar
1001     |    1    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50
1001     |    2    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50
1001     |    3    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50

But I want like

S_Adm_No | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar
1001     | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150

please any one help me

my code are below..

What I have tried:

SELECT FA.S_Adm_No,FA.[FHeadId],
FA.APR - ISNULL(FET.APR,0) AS APR,
FA.MAY - ISNULL(FET.MAY,0) AS MAY,
FA.JUN - ISNULL(FET.JUN,0) AS JUN,
FA.JUL - ISNULL(FET.JUL,0) AS JUL,
FA.AUG - ISNULL(FET.AUG,0) AS AUG,
FA.Sept - ISNULL(FET.SEP,0) AS SEP,
FA.OCT - ISNULL(FET.OCT,0) AS OCT,
FA.NOV - ISNULL(FET.NOV,0) AS NOV,
FA.DEC - ISNULL(FET.DEC,0) AS DEC,
FA.JAN - ISNULL(FET.JAN,0) AS JAN,
FA.FEB - ISNULL(FET.FEB,0) AS FEB,
FA.MAR - ISNULL(FET.MAR,0) AS MAR
FROM Fee_Assign_to_Students FA LEFT JOIN  
							 (SELECT FE.S_Adm_No,FE.[FHeadId],
									 SUM(FE.E_Apr) AS APR,
									 SUM(FE.E_MAY) AS MAY,
									 SUM(FE.E_JUN) AS JUN,
									 SUM(FE.E_JUL) AS JUL,
									 SUM(FE.E_AUG) AS AUG,
									 SUM(FE.E_SEP) AS SEP,
									 SUM(FE.E_OCT) AS OCT,
									 SUM(FE.E_NOV) AS NOV,
									 SUM(FE.E_DEC) AS DEC,
									 SUM(FE.E_JAN) AS JAN,
									 SUM(FE.E_FEB) AS FEB,
									 SUM(FE.E_MAR) AS MAR 
							FROM Fee_Assign_Waiver_to_Students FE 
							GROUP BY  FE.S_Adm_No,FE.FHeadId) 
							AS FET ON (FA.S_Adm_No=FET.S_Adm_No) AND (FA.FHeadId=FET.FHeadId)WHERE FA.S_Adm_No='1001'

解决方案

I already answered to your similar question here: How to make pivot table in sqlserver[^]

On the first look - wrong approach! The structure of both tables seems to be the same. Why?

Due to the type of join a result set may differ. Please, read this: Visual Representation of SQL Joins[^].

I'd use UNION ALL:

SELECT T.S_Adm_No,  SUM(T.Apr) AS Apr, SUM(T.May) AS May, SUM(T.Jun) As Jun, SUM(T.Jul) As Jul, SUM(T.Aug) AS Aug, SUM(T.Sep) As Sep, 
     SUM(T.Oct) AS Oct, SUM(T.Nov) AS Nov, SUM(T.Dec) AS Dec, SUM(T.Jan) AS Jan, SUM(T.Feb) AS Feb, SUM(T.Mar) AS Mar
FROM (
SELECT FAS.*
FROM Fee_Assign_to_Students AS FAS 
UNION ALL 
SELECT FAW.*
FROM Fee_Assign_Waiver_to_Students AS FAW
) AS T
GROUP BY T.S_Adm_No

这篇关于如何合并所有行，然后合并sqlserver中所有列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！