时间戳

首页 > 解决方案 > 如何进行多值查询？

mysql - 如何进行多值查询？

问题描述

我使用 Maria DB。我有一个统计表，其中计算了用水操作。我有三种水0,2,3。但是有0型水，包括2型、3型和0型，也就是0型水=0+2+3。0型水怎么取样，应该是这样的0=0- 2-3.我有一个周期标记，当周期为 1 时，我保持计数。我编写查询并且它可以工作，但我需要使用一些statistical_id我尝试使用函数IN并仅获得最小值 (3133) 的结果，为什么？

id  statistical_id  period  water water_type    created_at
29229825    3133    1   37.50   0   2020-10-26 22:00:00
29229827    3133    1   30.00   3   2020-10-26 22:00:00
29228803    3144    1   57.50   0   2020-10-26 22:00:00
29238998    3133    1   1.50    2   2020-10-26 22:00:00
29228811    3144    1   51.50   3   2020-10-26 22:00:00
29228807    3144    1   2.50    2   2020-10-26 22:00:00
29222427    3145    1   0.00    0   2020-10-25 22:00:00
29214408    3144    1   2.50    2   2020-10-25 22:00:00
29214183    3144    1   56.50   3   2020-10-25 22:00:00
29214179    3144    1   62.50   0   2020-10-25 22:00:00
29214971    3133    1   31.50   0   2020-10-25 22:00:00
29215195    3133    1   3.00    2   2020-10-25 22:00:00
29214974    3133    1   20.00   3   2020-10-25 22:00:00
29199695    3144    1   122.00  0   2020-10-24 21:00:00
29200438    3133    1   20.00   0   2020-10-24 21:00:00

和结果

device_id water_type `date` water
    3133    0   2020-10-25  8.5
    3133    0   2020-10-26  6

询问

select statistical_id device_id, water_type, date(CONVERT_TZ (created_at,'+00:00','+02:00')) date,
sum(case when water_type = 0 then water
                    else - water
               end) water
    from `production`.statistics 
    where statistical_id in (3144, 3133, 3145)
          and created_at >= '2020-10-04 22:00:00'
          and created_at <= '2020-10-15 22:00:00'
          and period = 1
          and water_type in (0, 2, 3)
          group by date(created_at);

标签： mysqlsqlgroup-bysummariadb

解决方案

你只是想要聚合吗？

select statistical_id,
       sum(case when water_type = 0 then water
                else - water
           end)
from `production`.statistics 
where statistical_id in (3144, 3133, 3145) and
      created_at >= '2020-10-04 21:00:00' and
      created_at <= '2020-10-15 21:00:00' and
      period = 1 and
      water_type in (0, 2, 3)
group by statistical_id

推荐阅读