时间戳

首页 > 解决方案 > 如何在sql查询中对一个字段中的多个字段求和？

sql - 如何在sql查询中对一个字段中的多个字段求和？

问题描述

我在输入中有很多代码，这个代码有一个父

SELECT [fromAccCode]= 
CASE WHEN [fromAccCode] IN (201010, 201020, 201442, 201521, 201611,201612,209050,
209060,209090,209110,213040,213163,213165,213166,213190) THEN r1
WHEN [fromAccCode] IN (201040, 201048, 201050, 201090, 201120, 201150
,201155) THEN r2
WHEN [fromAccCode] IN (201443,206020,207010,207040,207100,207130,209612
) THEN r3

我用这段代码设置输入

SELECT [fromAccCode] FROM [dbo].[source_table] WHERE ','+'201010, 
201020,259861,754265,213040,213163,213165,201040,
201048, 201050,201443,206020'+',' like '%,'+cast([fromAccCode] 
AS varchar(10))+',%'

我希望您有值（）、值总和（）和值总和（）的3记录1,2,3形式，并且此字段具有值sumr1r2r3

标签： sqlsql-server

解决方案

最初我假设您可以拆分 WHEN 然后对它们求和。

SELECT 
SUM(CASE WHEN [fromAccCode] IN (201010, 201020, 201442, 201521, 201611, 201612, 209050, 209060, 209090, 209110, 213040, 213163, 213165, 213166, 213190) THEN r1 END) AS TotalR1,
SUM(CASE WHEN [fromAccCode] IN (201040, 201048, 201050, 201090, 201120, 201150, 201155) THEN r2 END) AS TotalR2,
SUM(CASE WHEN [fromAccCode] IN (201443, 206020, 207010, 207040, 207100, 207130, 209612) THEN r3 END) AS TotalR3
FROM [dbo].[source_table]
WHERE [fromAccCode] IN (
  201010, 201020, 201442, 201521, 201611, 201612, 209050, 209060, 209090, 209110, 213040, 213163, 213165, 213166, 213190,
  201040, 201048, 201050, 201090, 201120, 201150, 201155,
  201443, 206020, 207010, 207040, 207100, 207130, 209612);

但你似乎在寻找类似这种怪物的东西。

SELECT src.fromAccCode,
(CASE 
 WHEN ref.TypeR = 1 THEN src.r1
 WHEN ref.TypeR = 2 THEN src.r2
 WHEN ref.TypeR = 3 THEN src.r3 
 END) AS rx,
SUM([value]) as SumValues
FROM [dbo].[source_table] as src
JOIN (SELECT DISTINCT fromAccCode, TypeR FROM (VALUES 
 (201010,1), (201020,1), (201442,1), (201521,1), (201611,1), (201612,1), (209050,1), (209060,1), (209090,1), (209110,1), 
 (213040,1), (213163,1), (213165,1), (213166,1), (213190,1),
 (201040,2), (201048,2), (201050,2), (201090,2), (201120,2), (201150,2), (201155,2),
 (201443,3), (206020,3), (207010,3), (207040,3), (207100,3), (207130,3), (209612,3)
) val (fromAccCode, TypeR)) AS ref ON ref.fromAccCode = src.fromAccCode
GROUP BY src.fromAccCode,
(CASE 
 WHEN ref.TypeR = 1 THEN src.r1
 WHEN ref.TypeR = 2 THEN src.r2
 WHEN ref.TypeR = 3 THEN src.r3 
 END);

顺便说一句，关于那个 LIKE 标准。
使用 CONCAT 而不是强制转换为 varchar 也可以。

WHERE ',' + '201010,201020,259861,754265,213040,213163,213165,201040,201048,201050,201443,206020' + ',' 
      LIKE CONCAT('%,', [fromAccCode], ',%')

推荐阅读