我想要每个月和每个site_id的最小值。
我想要每个月和每个site_id的最小值。
提问于 2022-08-09 07:40:19
第一表: site_map_pae
id | site_id | site_name | off_id
1 213140951 Med 3
2 213140952 Vedmed 3第二表:电力
id | timeIn | energy | site_id
1 2022-07-07 07:20:35 150 213140951
2 2022-07-07 10:46:23 155 213140952
3 2022-07-09 18:37:23 160 213140951
4 2022-08-01 14:46:23 170 213140951
5 2022-08-03 19:46:23 180 213140952
6 2022-08-06 20:46:23 190 213140952
7 2022-08-09 13:37:23 200 213140951*我想要达到的结果是这样的.
id | timeIn | result_energy
1 2022-07-09 18:37:23 10
2 2022-08-09 13:37:23 40 ，这是我的查询。我希望通过表off_id = "3“连接site_map_pae.
`
SELECT Max(DISTINCT energy)-MIN(DISTINCT energy) as result_energy, max(timeIn) as timeIn
FROM Electric.site_map_pae AS t1
INNER JOIN Electric.electric AS t2
ON t1.site_id = t2.site_id
WHERE t1.off_id = "3"
AND t2.timeIn BETWEEN '2022-01-01 00:00:00' and '2022-12-31 23:59:59'`
我的研究结果：
id | result_energy | timeIn
1 50.00 2022-08-09 13:37:23回答 1
Stack Overflow用户
发布于 2022-08-09 08:25:35
查询中没有分组，所以它只显示1行。
因为每个月都需要它，所以每个site_id都需要按月和site_id分组。
MONTH(timeIn)将从timeIn字段获得这个月。
SELECT Max(DISTINCT energy)-MIN(DISTINCT energy) as result_energy,MONTH(timeIn) as timeIn_month, t1.site_id
FROM Electric.site_map_pae AS t1
INNER JOIN Electric.electric AS t2
ON t1.site_id = t2.site_id
WHERE t1.off_id = "3"
AND t2.timeIn BETWEEN '2022-01-01 00:00:00' and '2022-12-31 23:59:59'
GROUP BY MONTH(timeIn), t1.site_id就像这样，希望能帮上忙。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73288143
复制相关文章
相似问题
腾讯云开发者
