如何在Sql Server中汇总列 [英] How Do I Sum The Column In Sql Server

问题描述

HI all，

我如何根据1到12,13到24之间的欠款来计算收款金额。

集合advisor_Code Plan_name advisor_id到期否
  3200   1010200022  GIFDL  -   36   127   1  
  1000   1010200022  GIFDL  -   36   127   2  
  1000   1010200022  GIFDL  -   36   127   3  
  1000   1010200022  GIFDL  -   36   127   4  
  5000   1010200022  GIFDL  -   36   127   12  
  5000   1010200022  GIFDL  -   36   127   13  

i我期待

集合advisor_Code Plan_name advisor_id到期没有
  6200   1010200022  GIFDL  -   36   127  1-12 
 
  10000   1010200022  GIFDL  -   36   127  12-24 

解决方案

首先，你说你想要1号，12号，13号等24号，但是你想要的是结果[到期号码]是12-24，而不是13-24 ...



这是我的样本（1-12,13-24,25-36）：

  CREATE   TABLE  #TempData 
（[Collection]  INT ，
 [advisor_Code]  VARCHAR （ 50 ），
 [Plan_name]  VARCHAR （ 50  ），
 [advisor_id]  INT ，
 [Due_No]  INT ）; 
 
  INSERT   INTO  #TempData（[Collection]，[advisor_Code]，[ Plan_name]，[advisor_id]，[Due_No]）
  VALUES （ 3200 ，'  1010200022'，'  GIFDL  -  36'， 127 ， 1 ），
 （ 1000 ，'  1010200022'，< span class =code-string>'  GIFDL  -  36'， 127 ，< span class =code-digit> 2 ），
（ 1000 ，'  1010200022'，'  GIFDL  -  36'， 127 ， 3 ），
（ 1000 ，'  1010200022'，'  GIFDL  - 36'， 127 ， 4 ），
（ 5000 ，'  1010200022'，'  GIFDL  -  36'， 127 ， 12 ），
（ 5000 ，'  1010200022'，'  GIFDL  -  36'， 127 ， 13 ）; 
 
 
;  WITH 范围
  AS （< span class =code-keyword> SELECT   1   AS  [RangeFrom]，
  12   AS  [RangeTo] 
  UNION   ALL  
  SELECT （[RangeTo] +  1 ） AS  [RangeFrom]，
（[RangeTo] +  12 ） AS  [RangeTo] 
  FROM 范围
  WHERE  [RangeTo]<  36 ）
  SELECT  SUM（[Collection]） AS  [Collection]，
 [advisor_Code]，
 [Plan_name]，
 [advisor_id]，
 CAST（[RangeFrom]  AS   VARCHAR （ 10 ））+ '   - ' + CAST（[RangeTo]  AS   VARCHAR （ 10 ）） AS  [Due_No] 
  FROM 范围 AS  r 
  LEFT   OUTER   JOIN  #TempData  AS  t 
  ON  t。[Due_No]> = r。[RangeFrom] 
  AND  t。[Due_No]< = r。[RangeTo] 
  GROUP   BY  [RangeFrom]，
 [RangeTo]，
 [advisor_Code]，
 [Plan_name]，
 [advisor_id] 
  OPTION （MAXRECURSION  32767 ）; 
 
 
  DROP   TABLE  #TempData; 

Max [Due_No]限制为36（ WHERE [RangeTo]< 36 ）。您可以使用表格或表格变量代替CTE。



结果：

 集合advisor_Code Plan_name advisor_id Due_No  
 11200 1010200022 GIFDL  -  36 127 1-12 
 5000 1010200022 GIFDL  -  36 127 13-24 
 NULL NULL NULL NULL 25-36 

HI all,
how do i sum the collection amount based on the Due no's like 1 to 12 , 13 to 24,..

Collection	advisor_Code	Plan_name	advisor_id	Due No
3200	        1010200022  	GIFDL - 36	127	        1
1000    	1010200022	GIFDL - 36	127      	2
1000	        1010200022	GIFDL - 36	127	        3
1000	        1010200022	GIFDL - 36	127      	4
5000            1010200022	GIFDL - 36      127      	12
5000            1010200022	GIFDL - 36      127      	13

i am expecting

Collection  advisor_Code    Plan_name   advisor_id  Due No
6200         1010200022   GIFDL - 36    127         1-12

10000        1010200022  GIFDL - 36     127         12-24

解决方案

Hi,

First of all, you said that you want Due No's like 1 to 12, 13 to 24, etc., but in your expected result [Due No] is 12-24, not 13-24...

Here's my sample (1-12, 13-24, 25-36):

CREATE TABLE #TempData
  ([Collection]   INT,
   [advisor_Code] VARCHAR(50),
   [Plan_name]    VARCHAR(50),
   [advisor_id]   INT,
   [Due_No]       INT);

INSERT INTO #TempData ([Collection], [advisor_Code], [Plan_name], [advisor_id], [Due_No])
VALUES	(3200, '1010200022', 'GIFDL - 36', 127, 1),
	(1000, '1010200022', 'GIFDL - 36', 127, 2),
	(1000, '1010200022', 'GIFDL - 36', 127, 3),
	(1000, '1010200022', 'GIFDL - 36', 127, 4),
	(5000, '1010200022', 'GIFDL - 36', 127, 12),
	(5000, '1010200022', 'GIFDL - 36', 127, 13);


;WITH Ranges
     AS (SELECT 1  AS [RangeFrom],
                12 AS [RangeTo]
         UNION ALL
         SELECT ( [RangeTo] + 1 )  AS [RangeFrom],
                ( [RangeTo] + 12 ) AS [RangeTo]
         FROM   Ranges
         WHERE  [RangeTo] < 36)
SELECT SUM([Collection]) AS [Collection],
       [advisor_Code],
       [Plan_name],
       [advisor_id],
       CAST([RangeFrom] AS VARCHAR(10)) + '-' + CAST([RangeTo] AS VARCHAR(10)) AS [Due_No]
FROM   Ranges AS r
       LEFT OUTER JOIN #TempData AS t
                    ON t.[Due_No] >= r.[RangeFrom]
                       AND t.[Due_No] <= r.[RangeTo]
GROUP  BY [RangeFrom],
          [RangeTo],
          [advisor_Code],
          [Plan_name],
          [advisor_id]
OPTION (MAXRECURSION 32767); 


DROP TABLE #TempData;

Max [Due_No] is limited to 36 (WHERE [RangeTo] < 36). Instead of CTE you can use a table or table variable.

Result:

Collection	advisor_Code	Plan_name	advisor_id	Due_No
11200		1010200022	GIFDL - 36	127		1-12
5000		1010200022	GIFDL - 36	127		13-24
NULL		NULL		NULL		NULL		25-36

这篇关于如何在Sql Server中汇总列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！