SQL：添加不同行中的所有值

发布于 2024-09-26 03:34:44 字数 1144 浏览 0 评论 0原文

我想将两个 SQL 查询合并为一个。第一个选择我需要在第二个查询中添加的行的所有 ID：

SELECT t.mp_id FROM t_mp AS t
JOIN t_mp_og USING (mp_id)
WHERE og_id = 2928
AND t.description = 'Energy'

第二个应该将第一个查询返回的行的值加在一起。到目前为止，我只能添加几个选择，并在它们之间添加一个+。对于添加查询一返回的所有行的动态查询，我想做一些相当于“foreach(value from query1){ sum += value }”的操作并返回该总和。

SELECT(

(SELECT current_value FROM t_value_time WHERE mp_id = 29280001 AND time_id =
(SELECT time_id FROM t_time WHERE time_stamp = 
(SELECT max(time_stamp) FROM v_value AS v WHERE time_stamp is not null AND mp_id = 29280001)))
+
(SELECT current_value FROM t_value_time WHERE mp_id = 29280015 AND time_id =
(SELECT time_id FROM t_time WHERE time_stamp = 
(SELECT max(time_stamp) FROM v_value AS v WHERE time_stamp is not null AND mp_id = 29280015)))
+
(SELECT current_value FROM t_value_time WHERE mp_id = 29280022 AND time_id =
(SELECT time_id FROM t_time WHERE time_stamp = 
(SELECT max(time_stamp) FROM v_value AS v WHERE time_stamp is not null AND mp_id = 29280022)))

);

我的两个问题：我不知道如何添加一组中的所有行，只能手动“+”方式。我也不知道如何将行中的 ID 放入 SELECT 中获取值。我尝试过 AS，但它似乎只适用于表，而不适用于单个值。

谢谢你的帮助，先生B

原文

I have two SQL queries I want to combine into one. The first one selects all the IDs of the rows I need to add in the second one:

SELECT t.mp_id FROM t_mp AS t
JOIN t_mp_og USING (mp_id)
WHERE og_id = 2928
AND t.description = 'Energy'

The second one should add together the values from the rows returned by the first query. Up until now I've only been able to add several selects with a + in between them. For a dynamic query that adds all the rows returned by query one, I'd like to do something equivalent to "foreach(value from query1){ sum += value }" and return that sum.

SELECT(

(SELECT current_value FROM t_value_time WHERE mp_id = 29280001 AND time_id =
(SELECT time_id FROM t_time WHERE time_stamp = 
(SELECT max(time_stamp) FROM v_value AS v WHERE time_stamp is not null AND mp_id = 29280001)))
+
(SELECT current_value FROM t_value_time WHERE mp_id = 29280015 AND time_id =
(SELECT time_id FROM t_time WHERE time_stamp = 
(SELECT max(time_stamp) FROM v_value AS v WHERE time_stamp is not null AND mp_id = 29280015)))
+
(SELECT current_value FROM t_value_time WHERE mp_id = 29280022 AND time_id =
(SELECT time_id FROM t_time WHERE time_stamp = 
(SELECT max(time_stamp) FROM v_value AS v WHERE time_stamp is not null AND mp_id = 29280022)))

);

My two problems: I don't know how to add all rows in a set, only the manual "+" way. I also don't know how to put the ID from the row into the SELECT getting the value. I've tried AS, but it seems to only work for tables, not single values.

Thanks for you help,
MrB

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

好菇凉咱不稀罕他 2024-10-03 03:34:44

这是编辑后的查询，

select t.mp_id,sum(current_value)
from t_value_time t, t_time tim, v_value v
where 
where t.mp_id = v.mp_id
    and v.time_stamp is not null
    and tim.time_stamp = MAX(v.time_stamp)
    and t.time_id=tim.time_id
    and t.mp_id in ( 29280001,29280015,29280022)

group by t.mp_id

使用 SUM() 进行聚合

here is the edited query

select t.mp_id,sum(current_value)
from t_value_time t, t_time tim, v_value v
where 
where t.mp_id = v.mp_id
    and v.time_stamp is not null
    and tim.time_stamp = MAX(v.time_stamp)
    and t.time_id=tim.time_id
    and t.mp_id in ( 29280001,29280015,29280022)

group by t.mp_id

use SUM() for aggregation

回复收藏 0 原文