"【Question】 I got a problem with limited left join, what I want is for every row in the first tab .."

blackduckie RaqForum 28 No.
449 View • 5 Years ago

Order-based Calculations – Join Up Neighboring Records

SQL(179)

【Question】
I got a problem with limited left join, what I want is for every row in the first table get only one result from the second table.

Here is my code returning all:

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 tried to limit it, but got 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 is an example of the table:

id number machine_id type 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

The expected result:

id endmachine_id number begin machine_id number

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

EDIT:

My query (the 1^st one) is working but it gives more result than I want, so I need only to limit it to get only one row from the 2^nd table with 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 type=‘1’(end) first.

Below is the result I got right now to compare with the 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

The 2^nd row is unwanted.

【Answer】
Your question involves order-based calculations. It’s not easy to perform them in SQL. So you can do it in esProc SPL (Structured Process Language) with only a several-liner script:

	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)