mysql - 连接同一张表会产生太多结果

Question

我的脑子一直在思考为什么我在以下查询中得到太多结果:

基于以下 2 个表格:

P4查询:

+------------+----+-----------------+--------+----------+
| Date       | Id | MeteringPointId | Reason | StatusId |
+------------+----+-----------------+--------+----------+
| 2013-08-29 | 60 |               2 | DAY    |       60 |
| 2013-08-29 | 59 |               2 | INT    |       59 |
| 2013-08-29 | 50 |               3 | DAY    |       50 |
| 2013-08-28 | 58 |               3 | DAY    |       58 |
| 2013-08-28 | 57 |               3 | INT    |       57 |
| 2013-08-28 | 56 |               2 | DAY    |       56 |
| 2013-08-28 | 55 |               2 | INT    |       55 |

还有P4阅读:

+-------------+------+---------+----------+---------------------+------+-------------+
| EnergyMeter | Id   | QueryId | Register | Time                | Unit | Value       |
+-------------+------+---------+----------+---------------------+------+-------------+
| 15524067    | 2406 |      59 | 2.8.0    | 2013-08-29 23:30:00 | WH   |       0.000 |
| 15524067    | 2609 |      59 | 1.8.0    | 2013-08-29 23:30:00 | WH   | 5959289.000 |
| 15524067    | 2243 |      59 | 2.8.0    | 2013-08-29 23:15:00 | WH   |       0.000 |
| 15524067    | 2448 |      59 | 1.8.0    | 2013-08-29 23:15:00 | WH   | 5959179.000 |

我使用以下查询在 P4Reading 表(同一个表)上创建 JOIN 来获取前 15 分钟的值。这很有效，但我得到了很多值(value)观。我看起来缺少 join 或 where 语句，但无法掌握它。我不知道问题从何而来。

SELECT `P4Query`.`MeteringPointId`, `table1`.`EnergyMeter`, `table1`.`Register`,
       `table1`.`Time`, `table1`.`Unit`, `table1`.`Value`,
      (`table2`.`Value` - `table1`.`Value`)/1000 as totaal
FROM P4Query, P4Reading table1
INNER JOIN P4Reading table2 ON `table1`.`Time` = (`table2`.`Time` - INTERVAL 15 minute)
WHERE (`P4Query`.`Id` = `table1`.`QueryId`)
ORDER BY `table1`.`Time` DESC;

我期望以下内容:

+-----------------+-------------+----------+---------------------+------+-------------+---------------+
| MeteringPointId | EnergyMeter | Register | Time                | Unit | Value       | totaal        |
+-----------------+-------------+----------+---------------------+------+-------------+---------------+
|               2 | 15524067    | 1.8.0    | 2013-08-29 23:30:00 | WH   | 5959289.000 |     0.1380000 |
|               2 | 15524067    | 2.8.0    | 2013-08-29 23:30:00 | WH   |       0.000 |     0.0000000 |
|               2 | 15524067    | 1.8.0    | 2013-08-29 23:15:00 | WH   | 5959179.000 |     0.1100000 |
|               2 | 15524067    | 2.8.0    | 2013-08-29 23:15:00 | WH   |       0.000 |     0.0000000 |

但我得到:

+-----------------+-------------+----------+---------------------+------+-------------+---------------+
| MeteringPointId | EnergyMeter | Register | Time                | Unit | Value       | totaal        |
+-----------------+-------------+----------+---------------------+------+-------------+---------------+
|               2 | 15524067    | 2.8.0    | 2013-08-29 23:30:00 | WH   |       0.000 |  5959.4270000 |
|               2 | 15524067    | 1.8.0    | 2013-08-29 23:30:00 | WH   | 5959289.000 | -5959.2890000 |
|               2 | 15524067    | 1.8.0    | 2013-08-29 23:30:00 | WH   | 5959289.000 |     0.1380000 |
|               2 | 15524067    | 2.8.0    | 2013-08-29 23:30:00 | WH   |       0.000 |     0.0000000 |
|               2 | 15524067    | 1.8.0    | 2013-08-29 23:15:00 | WH   | 5959179.000 |     0.1100000 |
|               2 | 15524067    | 2.8.0    | 2013-08-29 23:15:00 | WH   |       0.000 |  5959.2890000 |
|               2 | 15524067    | 1.8.0    | 2013-08-29 23:15:00 | WH   | 5959179.000 | -5959.1790000 |
|               2 | 15524067    | 2.8.0    | 2013-08-29 23:15:00 | WH   |       0.000 |     0.0000000 |

我准备好了 fiddle :http://sqlfiddle.com/#!2/c2bdf/1

Answer 1

这是带有表别名的查询，因此更易于阅读:

SELECT pq.`MeteringPointId`, pr1.`EnergyMeter`, pr1.`Register`, pr1.`Time`, pr1.`Unit`,
       pr1.`Value`, (pr2.`Value` - pr1.`Value`)/1000 as totaal
FROM P4Query pq, P4Reading pr1 INNER JOIN
     P4Reading pr2
     ON pr1.`Time` = (pr2.`Time` - INTERVAL 15 minute)
WHERE (pq.`Id` = pr1.`QueryId`)
ORDER BY pr1.`Time` DESC;

您正在混合连接格式。看起来您还缺少 pr1 和 pr2 之间的 id 条件。这可能就是您想要的:

SELECT pq.`MeteringPointId`, pr1.`EnergyMeter`, pr1.`Register`, pr1.`Time`, pr1.`Unit`,
       pr1.`Value`, (pr2.`Value` - pr1.`Value`)/1000 as totaal
FROM P4Query pq join
     P4Reading pr1
     on pq.`Id` = pr1.`QueryId` join
     P4Reading pr2
     ON pq.`Id` = pr2.`QueryId` and
        pr1.`Time` = (pr2.`Time` - INTERVAL 15 minute)
ORDER BY pr1.`Time` DESC;

这添加了额外的连接条件并使连接语法保持一致。

我对这种准时情况有点怀疑 (pr1.Time = (pr2.Time - INTERVAL 15 分钟))。这需要时间值的绝对精度。在许多情况下，即使是规则间隔的时间也可能会偏差几毫秒——导致等式失败。