SQL中特定值的总和 [英] Sum of specific values in SQL

问题描述

我正在尝试使用SQL查找表中特定值的总和.样本表是:

I'm trying to find the sum of specific values within a table, using SQL. A sample table is:

+-------+-------+-------+-------+-------+-------+-------+
|  ID   |  Co1  |  Va1  |  Co2  |  Va2  |  Co3  |  Va3  |
+-------+-------+-------+-------+-------+-------+-------+
|  01   |  AA1  |   23.0|  AA2  |   11.2|  AA3  | 328.34|
|  02   |  AA2  |   27.0|  AA3  | 234.56|  AA4  |   23.8|
|  03   |  AA1  | 409.01|  AA4  | 234.98| NULL  | NULL  |
+-------+-------+-------+-------+-------+-------+-------+

我有35个代码"列和值.

I have 35 such 'Code' columns and values.

我需要选择一个只有一个代码的表.假设我需要代码AA3，这就是(这里不需要代码"列，而只是显示我从哪里得到值):

What I need is selecting a table having only one code. Say I need Code AA3, this would be (the Code column is not required here, but only to show where I got the values):

+-------+-------+--------+
|  ID   | Code  |  Value |
+-------+-------+--------+
|  01   |  AA3  |  328.34|
|  02   |  AA3  |  234.56|
|  03   |  AA3  |       0|
+-------+-------+--------+

然后我将需要另一个(单独的)查询，其中包含几个代码的总和，例如代码AA1和AA2的总和.

And I will later need another (separate) query which contains the sum of several codes, for example the sum of codes AA1 and AA2 together.

+-------+---------+
|  ID   |   Value |
+-------+---------+
|  01   |     34.2|
|  02   |     27.0|
|  03   |   409.01|
+-------+---------+

我当时正在考虑使用"WHERE"并在每35列上运行一次，但这确实很乏味，而且似乎效率不高.我想知道是否有一种方法可以通过SQL来做到这一点(我可以使用带有if语句的Excel来做到这一点，而且效果很好).

I was thinking about having a 'WHERE' and run on every 35 columns, but that would really be tedious and this does not seem too efficient. I was wondering if there was a way to do that through SQL (I can do it with Excel with an if statement and it works wonders).

也许有一种方法可以搜索"一行，返回列名，然后使用该列名来检索数字?

Maybe if there was a way to 'search' through a row, return the column name and then use that column name to retrieve the number?

让我知道是否需要更多信息=)

Let me know if you require more information =)

推荐答案

您应该做的是以规范化格式存储数据.

但是...

select SUM(v)
from
(

select * -- ID, r, v 
from 
(select * from yourtable) u
unpivot
( r for co in (co1, co2, co3)) as u1
unpivot
(  v for va in (va1, va2, va3)) as u2
where RIGHT(co,1) = RIGHT(va,1)
) v
WHERE R = 'AA1' -- etc

将返回您寻求的结果

这篇关于SQL中特定值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！