各位好，目前有個問題是希望查詢一分鐘有3次以上的交易，
每筆交易為基準後兩次交易時間要在1分內，
以範例來說 希望可以查出 001,005,007這三張單
雖然007往下兩張的交易時間是00:02:10超過1分鐘
但是在001那次查詢的時候就已經被劃分為需要的資料
原本的資料
+-----------+---------------------+
| 交易單號 | datetime |
+-----------+---------------------+
| 001 | 2019-01-01 00:00:00 |
| 005 | 2019-01-01 00:00:30 |
| 007 | 2019-01-01 00:00:59 |
| 008 | 2019-01-01 00:01:42 |
| 050 | 2019-01-01 00:02:10 |
+-----------+---------------------+
接下來我自己的預想是先將下兩筆的時間查詢出來再做timediff 如下表
雖然有邦友告訴我有lead()這個函式，不過很可惜sever版本才5.5.6.... lead()要到8才支援ORZ...
想要得到的資料
+----+----------+--------------------------------+---------------------+
| id | datetime | 下一筆datetime | 下兩筆datetime |
+----+---------------------+---------------------+---------------------+
| 1 | 2019-01-01 00:00:00 | 2019-01-01 00:00:30 | 2019-01-01 00:00:59 |
| 2 | 2019-01-01 00:00:30 | 2019-01-01 00:00:59 | 2019-01-01 00:01:42 |
| 3 | 2019-01-01 00:00:59 | 2019-01-01 00:01:42 | 2019-01-01 00:02:10 |
| 4 | 2019-01-01 00:01:42 | 2019-01-01 00:02:10 | NULL |
| 5 | 2019-01-01 00:02:10 | NULL | NULL |
+----+---------------------+---------------------+---------------------+
然後我依照這個思路想出了如下的查詢，但是發現子查詢出來的表a沒辦法拿給其他子查詢使用....
SET @row = 0;
SET @row1 = 1;
SELECT *
,(SELECT datetime FROM a WHERE novalue = @row1 ORDER BY novalue LIMIT 0,1) as next_time1
,(SELECT datetime FROM a WHERE novalue = @row1+1 ORDER BY novalue LIMIT 0,1) as next_time2
FROM
(
SELECT @row:=@row+1 AS novalue, trxn.*
FROM trxn
) AS a
感謝各位看了這麼落落長的問題描述(心路歷程?)之後，最後小弟想要請教一下
1.我這個使用變數來查詢的思路是否正確只是語法錯誤? 如果是要怎麼改寫才能夠讓他正常執行?
2.有沒有其他辦法來解這個問題?
已邀請的邦友 {{ invite_list.length }}/5
不是固定時間內的浮動基數資料。
這確實有點麻煩
我看你用+1記錄的方式。
其實我會用+1分的方式來取數量有無超過2筆。
目前暫時是這樣想。只是我不太明白你後面的說明。
還有要列出來的資料情況。
create table it200310a (
id int unsigned not null auto_increment primary key
, txn char(3) not null
, txdt datetime not null
);
insert into it200310a(txn, txdt) values
('001', timestamp '2019-01-01 00:00:00'),
('005', timestamp '2019-01-01 00:00:30'),
('007', timestamp '2019-01-01 00:00:59'),
('008', timestamp '2019-01-01 00:01:42'),
('050', timestamp '2019-01-01 00:02:10');
---
增加一筆資料,讓時間順序(txdt) 與 id, txn 錯位,
以確定後續語法的正確性.
insert into it200310a(txn, txdt) values
('009', timestamp '2019-01-01 00:01:30');
-----
依照 txdt 產生 流水序號
select txn
, txdt
, @n := @n + 1 as sn
from it200310a
, (select @n := 0) a
order by txdt;
+-----+---------------------+------+
| txn | txdt | sn |
+-----+---------------------+------+
| 001 | 2019-01-01 00:00:00 | 1 |
| 005 | 2019-01-01 00:00:30 | 2 |
| 007 | 2019-01-01 00:00:59 | 3 |
| 009 | 2019-01-01 00:01:30 | 4 |
| 008 | 2019-01-01 00:01:42 | 5 |
| 050 | 2019-01-01 00:02:10 | 6 |
+-----+---------------------+------+
6 rows in set, 2 warnings (0.00 sec)
經觀察,是依照 txdt 由小到大產生序號.
------------
接著就可以加工了.會很囉唆又無聊的一段.
select *
from (select txn
, txdt
, @an := @an + 1 as sn
from it200310a
, (select @an := 0) a
order by txdt
) i
left join
(select txn
, txdt
, @bn := @bn + 1 as sn
from it200310a
, (select @bn := 0) b
order by txdt
) j
on i.sn = j.sn - 1
left join
(select txn
, txdt
, @cn := @cn + 1 as sn
from it200310a
, (select @cn := 0) c
order by txdt
) k
on i.sn = k.sn - 2
;
各位好，目前有個問題是希望查詢一分鐘有3次以上的交易，
每筆交易為基準後兩次交易時間要在1分內
你這句話,不是很理解.
你把你真的想做到的,描述清楚一些,現在好像是繞彎路了.
又受教了，我之前還不知道 lead() 這個寶物，
類似問題都用土法練鋼，反正 sql 萬能。
看來，我的惰性還不小，只問能解決問題，
沒有再去研讀新觀念。
window functions 超好用喔.只是MySQL 以前一直停在
ANSI SQL92 標準,造成蠻多人誤以為資料庫就都是那樣.
SQL 大神啊 
抱歉回慢了，我昨天後來也是採用類似的解法，讓ID錯位再JOIN產生後面交易的時間，再做處理。
大概長這樣
SET @row = 0;
SET @row1 = -1;
SET @row2 = -2;
SELECT *
FROM
(
SELECT @row:=@row+1 AS novalue, `trxn_id`, `trxn_date`
FROM `trxn`
) AS a
INNER JOIN
(
SELECT @row1:=@row1+1 AS novalue, `trxn_id`, `trxn_date`
FROM `trxn`
) AS b
INNER JOIN
(
SELECT @row2:=@row2+1 AS novalue, `trxn_id`, `trxn_date`
FROM `trxn`
) AS c
WHERE a.novalue = b.novalue
AND a.novalue = c.novalue
至於@一級屠豬士說的描述問題，我覺得我的描述能力還有待加強，常常被這樣說
現在在測試大大的解法，目前還沒完全看懂，SQL真是博大精深
iThome鐵人賽
看影片追技術