相邻记录间的运算

润乾软件

于 2022-06-17 09:33:44 发布

阅读量75

点赞数

分类专栏： JAVA计算文章标签： sql 定位记录排序按序访问

本文链接：https://blog.csdn.net/raqsoft/article/details/125327506

版权

JAVA计算专栏收录该内容

363 篇文章 2 订阅

订阅专栏

博主在尝试从`order_log`表中通过LEFT JOIN获取特定类型记录的匹配项时遇到问题。原始查询返回了多余的结果。为了解决这个问题，博主试图使用子查询进行LIMIT限制，但这导致只得到一个正确结果。期望的结果是每条类型为1的记录匹配一条类型为0的最新记录。提供的示例数据和期望输出展示了匹配条件。编辑中明确了需求是仅对LEFT JOIN的第二张表进行一行限制。

摘要由CSDN通过智能技术生成

【问题】
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)

A2：取出 type==1 的记录位置

A3：用 A2 取出的位置所在记录及其前一条记录拼出结果集