補充一下,也可以用自己產生編號的方式.
輸入跳號測試資料:
INSERT INTO test0720 VALUES
(18005, '2011-07-16', 7500),
(18015, '2011-07-26', 9000),
(18019, '2011-07-30', 11300);
另外的方式:
SELECT a.id AS id, a.tdate AS tdate, a.count AS 'count', coalesce(a.count - b.count, a.count) AS diff
FROM ( SELECT @inum := @inum+1 AS anum, i.id, i.tdate, i.count
FROM (SELECT id, tdate, count
FROM test0720 ORDER BY id) i,
(SELECT @inum :=0) r
)a
LEFT JOIN ( SELECT @jnum := @jnum+1 AS bnum, j.id, j.tdate, j.count
FROM (SELECT id, tdate, count
FROM test0720 ORDER BY id) j,
(SELECT @jnum :=0) s
)b
ON (b.bnum = a.anum - 1);
+-------+------------+-------+------+
| id | tdate | count | diff |
+-------+------------+-------+------+
| 18000 | 2011-07-11 | 2000 | 2000 |
| 18001 | 2011-07-12 | 5000 | 3000 |
| 18002 | 2011-07-13 | 6000 | 1000 |
| 18005 | 2011-07-16 | 7500 | 1500 |
| 18015 | 2011-07-26 | 9000 | 1500 |
| 18019 | 2011-07-30 | 11300 | 2300 |
+-------+------------+-------+------+
使用coalesce()主要是考慮到coalesce()是ANSI-SQL的函數,功能類似IFNULL()