得到区间间的数量

Question

这张我的桌子很简单

+-----------+----------------+-----------+
|     id    |       date     |   meter   |
------------+----------------+-----------+
|      1    |  2103-11-01    |   5       |
|      2    |  2103-11-10    |   8       |
|      4    |  2103-11-14    |   10      |
|      6    |  2103-11-20    |   18      |
|      7    |  2103-11-25    |   25      |
|      10   |  2103-11-29    |   30      |
+-----------+----------------+-----------+

我怎样才能在记录时间的两个范围内得到使用仪表的结果，比如：

+----------------+----------------+-------+-----+--------+ 
|    date1       |    date2       | start | end | amount | 
+----------------+----------------+-------+-----+--------+
|    2013-11-01  |    2013-11-10  |  5    |  8  |  3     | 
|    2013-11-10  |    2013-11-14  |  8    | 10  |  2     | 
|    2013-11-14  |    2013-11-20  | 10    | 18  |  8     | 
|    2013-11-20  |    2013-11-25  | 18    | 25  |  7     | 
|    2013-11-25  |    2013-11-29  | 25    | 30  |  5     | 
+----------------+----------------+-------+-----+--------+

Answer 1

编辑:我拿到了：

select meters1.date as date1, min(meters2.date) as date2, meters1.meter as start,
  meters2.meter as end, (meters2.meter - meters1.meter) as amount
  from meters meters1, meters meters2 where meters1.date < meters2.date
   group by date1;

产出：

+------------+------------+-------+-----+--------+
| date1      | date2      | start | end | amount |
+------------+------------+-------+-----+--------+
| 2013-11-01 | 2013-11-10 |     5 |   8 |      3 |
| 2013-11-10 | 2013-11-14 |     8 |  10 |      2 |
| 2013-11-14 | 2013-11-20 |    10 |  18 |      8 |
| 2013-11-20 | 2013-11-25 |    18 |  25 |      7 |
| 2013-11-25 | 2013-11-29 |    25 |  30 |      5 |
+------------+------------+-------+-----+--------+

原文：

这是实现目标的主要方式：

select meters1.date as date1, meters2.date as date2, meters1.meter as start,
  meters2.meter as end, (meters2.meter - meters1.meter) as amount
  from meters meters1, meters meters2 having date1 < date2 order by date1;

它的产出如下：

+------------+------------+-------+-----+--------+
| date1      | date2      | start | end | amount |
+------------+------------+-------+-----+--------+
| 2013-11-01 | 2013-11-10 |     5 |   8 |      3 |
| 2013-11-01 | 2013-11-20 |     5 |  18 |     13 |
| 2013-11-01 | 2013-11-29 |     5 |  30 |     25 |
| 2013-11-01 | 2013-11-14 |     5 |  10 |      5 |
| 2013-11-01 | 2013-11-25 |     5 |  25 |     20 |
| 2013-11-10 | 2013-11-20 |     8 |  18 |     10 |
| 2013-11-10 | 2013-11-29 |     8 |  30 |     22 |
| 2013-11-10 | 2013-11-14 |     8 |  10 |      2 |
| 2013-11-10 | 2013-11-25 |     8 |  25 |     17 |
| 2013-11-14 | 2013-11-25 |    10 |  25 |     15 |
| 2013-11-14 | 2013-11-20 |    10 |  18 |      8 |
| 2013-11-14 | 2013-11-29 |    10 |  30 |     20 |
| 2013-11-20 | 2013-11-25 |    18 |  25 |      7 |
| 2013-11-20 | 2013-11-29 |    18 |  30 |     12 |
| 2013-11-25 | 2013-11-29 |    25 |  30 |      5 |
+------------+------------+-------+-----+--------+

Answer 2

MySql

SELECT DATES.date1,
       DATES.date2,
       m1.meter as start,
       m2.meter as end,
       m2.meter - m1.meter as amount
FROM
  (SELECT date as date1,
       (SELECT min(date)
        FROM tableName t2
        WHERE t2.date > t1.date) as date2
   FROM tableName t1
  )DATES,
  tableName m1,
  tableName m2
WHERE DATES.date2 IS NOT NULL
  AND m1.date = DATES.date1
  AND m2.date = DATES.date2
ORDER BY DATES.date1

sqlFiddle在这里

在MS-SQL SERVER 2002中，将单词end改为"end"，因为它抱怨end附近的语法。

Answer 3

如果您使用的是MySQL，那么自连接在这里会很好。使用ON子句将表加入到自己，以确保不将相同的记录连接到自己。这将为您提供数据的((N * N) - N)排列，其中N是原始行数。

SELECT
    ...
FROM
    tableName first
JOIN
    tableName second
    ON first.id != second.id

然后，这一切都是关于正确的SELECT(包括计算两个meter值之间的差异)。要在您发布的结果集中获取列，您可能希望使用SELECT

first.date AS date1,
second.date AS date2,
first.meter AS start,
second.meter AS end,
ABS(first.meter - second.meter) AS amount

编辑啊，我明白了。我曾设想过类似于城市之间的里程图，这是你以前在路线图上看到的(在这些地图中，你会看到相同的城市在行和列中，而交叉口中的单元格则表示这两个城市之间的里程数)。

但是看起来你只是想比较一下从一个日期到下一个日期的值。如果是这样的话，你可以利用MySQL处理GROUP和ORDER的方式.但是要小心，因为我不确定这是否有保证：

mysql> SELECT 
    table1.date AS date1, 
    table2.date AS date2, 
    table1.meter AS start, 
    table2.meter AS end, 
    ABS(table1.meter - table2.meter) AS amount 
FROM tableName table1 
JOIN tableName table2 
    WHERE table2.date > table1.date 
GROUP BY table1.date 
ORDER BY table2.date - table1.date;
+---------------------+---------------------+-------+------+--------+
| date1               | date2               | start | end  | amount |
+---------------------+---------------------+-------+------+--------+
| 2103-11-25 00:00:00 | 2103-11-29 00:00:00 |    25 |   30 |      5 |
| 2103-11-10 00:00:00 | 2103-11-14 00:00:00 |     8 |   10 |      2 |
| 2103-11-20 00:00:00 | 2103-11-25 00:00:00 |    18 |   25 |      7 |
| 2103-11-14 00:00:00 | 2103-11-20 00:00:00 |    10 |   18 |      8 |
| 2103-11-01 00:00:00 | 2103-11-10 00:00:00 |     5 |    8 |      3 |
+---------------------+---------------------+-------+------+--------+
5 rows in set (0.00 sec)