如何编写MySQL查询计算指定日期间各房产的用水量

前提说明 #

我们先统一假设你的水表读数表基础结构如下，你可以对应替换成自己实际的表名和字段名：

• 表名：water_meter_readings
• 核心字段：
  • property_id：房产唯一标识
  • reading_date：读数录入日期，类型为DATE/DATETIME
  • meter_reading：当期水表读数，数值类型

方案1：临时查询区间用水量（不需要永久存储） #

适合单次查询场景，直接返回计算结果：

-- 自定义要查询的起止日期
SET @start_date = '2021-08-12';
SET @end_date = '2021-08-16';

SELECT
    property_id,
    (end_read.reading - start_read.reading) AS `consumed between 8/12 and 8/16`
FROM
    -- 提取所有房产起始日的读数
    (SELECT property_id, meter_reading AS reading FROM water_meter_readings WHERE reading_date = @start_date) AS start_read
-- 关联对应房产结束日的读数
INNER JOIN
    (SELECT property_id, meter_reading AS reading FROM water_meter_readings WHERE reading_date = @end_date) AS end_read
ON start_read.property_id = end_read.property_id;

如果存在部分房产在起止日没有读数的情况，可以把INNER JOIN改成LEFT JOIN，配合IFNULL函数处理空值即可。 #

方案2：永久存储计算结果到新列 #

如果需要把结果固化到表中，分两步操作：

步骤1：添加存储用水量的新列 #

ALTER TABLE water_meter_readings 
ADD COLUMN `consumed between 8/12 and 8/16` DECIMAL(10,2) DEFAULT NULL COMMENT '2021-08-12至2021-08-16用水量';

步骤2：更新新列的计算值 #

-- 自定义要查询的起止日期
SET @start_date = '2021-08-12';
SET @end_date = '2021-08-16';

UPDATE water_meter_readings t1
INNER JOIN (
    SELECT
        start_read.property_id,
        (end_read.reading - start_read.reading) AS consumed_val
    FROM
        (SELECT property_id, meter_reading AS reading FROM water_meter_readings WHERE reading_date = @start_date) AS start_read
    INNER JOIN
        (SELECT property_id, meter_reading AS reading FROM water_meter_readings WHERE reading_date = @end_date) AS end_read
    ON start_read.property_id = end_read.property_id
) t2 ON t1.property_id = t2.property_id
SET t1.`consumed between 8/12 and 8/16` = t2.consumed_val
-- 可根据需求调整更新的行范围，这里默认仅更新结束日的对应行
WHERE t1.reading_date = @end_date;

提示：如果你的表中同一个房产同一天有多条读数，建议在子查询中通过MAX(meter_reading)或者按时间排序取最新的读数后再计算差值。

内容的提问来源于stack exchange，提问作者lrgalvez