相邻记录间的运算
【问题】
I got a problem with limiting left join, what I want is to every row in first table get only one result from second table.
Here is my code without limiting:
SELECT * FROM
(
((SELECT id,date as end,machine_id,numer FROM `order_log` WHERE typ = 1)STOP
left join
(SELECT date as begin, machine_id, numer FROM `order_log` ST WHERE typ = 0 ORDER BY date DESC)START
ON START.begin < STOP.end AND START.machine_id = STOP.machine_id
AND START.numer = STOP.numer)
)
I also tried to limit it, but then I get only one correct result:
SELECT * FROM
(
((SELECT id,date as end,machine_id,numer FROM `order_log` WHERE typ = 1)STOP
left join
(SELECT date as begin, machine_id, numer FROM `order_log` ST WHERE typ = 0 ORDER BY date DESC)START
ON START.begin = (SELECT date FROM `order_log` WHERE date < STOP.end AND typ = 0 AND machine_id = STOP.machine_id AND numer = STOP.numer ORDER BY date DESC LIMIT 1) AND START.machine_id = STOP.machine_id
AND START.numer = STOP.numer)
)
Below example of table:
id numer machine_id typ date
1 31392 39 0 2015-05-26 15:44:56
2 31761 23 0 2015-05-26 16:12:53
3 31761 24 0 2015-05-26 16:14:03
4 31591 15 0 2015-05-26 16:15:02
5 31586 400 2015-05-26 16:15:46
6 31392 39 1 2015-05-26 16:16:19
7 31392 39 0 2015-05-26 16:16:19
8 31392 39 1 2015-05-28 08:15:26
9 31386 39 0 2015-05-28 08:15:26
10 31761 24 1 2015-06-02 00:40:07
11 31761 24 0 2015-06-02 00:40:07
12 31386 39 1 2015-06-02 13:11:13
13 31392 39 0 2015-06-02 13:11:13
And expected result:
id endmachine_id numer begin machine_id numer
6 2015-05-26 16:16:19 39 31392 2015-05-26 15:44:56 39 31392
10 2015-06-02 00:40:07 24 31761 2015-05-26 16:14:03 24 31761
8 2015-05-28 08:15:26 39 31392 2015-05-26 16:16:19 39 31392
12 2015-06-02 13:11:13 39 31386 2015-05-28 08:15:26 39 31386
Thanks in advance
EDIT:
To clarify, my query (1st one) is working but it gives more result than I want, so I need only to limit it to get only one row from 2nd table of left join. So to every row from
(SELECT id,date as end,machine_id,numer FROM \`order_log\` WHERE typ = 1)
I want to get one and only one row from
left join
(SELECT date as begin, machine_id, numer FROM \`order_log\` ST WHERE typ = 0 ORDER BY date DESC)START
ON START.begin = (SELECT date FROM \`order_log\` WHERE date < STOP.end AND typ = 0 AND machine\_id = STOP.machine\_id AND numer = STOP.numer ORDER BY date DESC LIMIT 1) AND START.machine\_id = STOP.machine\_id
AND START.numer = STOP.numer)
I need to get all existing pairs, but I am sure that if end exists begin also exists, that’s why I search for all typ=‘1’(end) first.
Below the result I got right now to compare with expected one:
2015-05-26 16:16:19 39 31392 2015-05-26 15:44:56 39 31392
2015-05-28 08:15:26 39 31392 2015-05-26 15:44:56 39 31392
2015-06-02 00:40:07 24 31761 2015-05-26 16:14:03 24 31761
2015-05-28 08:15:26 39 31392 2015-05-26 16:16:19 39 31392
2015-06-02 13:11:13 39 31386 2015-05-28 08:15:26 39 31386
2nd row is unwanted
【回答】
牵涉到有序运算，SQL 处理较麻烦。可以使用集算器实现，只需几句 SPL 即可搞定：
A |
|
1 |
$select * from tb1 order by number,machine_id,date |
2 |
=A1.pselect@a(type==1) |
3 |
=A2.new((r=A1(~)).id:id,r.date,r.machine_id,r.number,(pr=A1(~-1)).date:pdate,pr.machine_id,r.number) |
A3：用 A2 取出的位置所在记录及其前一条记录拼出结果集
集算器提供 JDBC 接口，可以像使用数据库一样，轻松嵌入到应用程序中，用起来很简单，可参考Java 如何调用 SPL 脚本。
