时间戳

首页 > 解决方案 > 如何避免sql语句中有两个max？

sql - 如何避免sql语句中有两个max？

问题描述

我有以下要执行的查询，但仍然有两个 Max 在一个 SQL 语句中。我想纠正这个问题。

SELECT * FROM persons s INNER JOIN   ( 
  SELECT s1.person_id, 
         MAX(s1.run_number * power(2,64)  + s1.transaction_id) as CEILING_ID, 
         MAX(message_sequence) as MSG_SEQUENCE  
  FROM persons S1 
  WHERE s1.run_number * power(2,64) + s1.transaction_id < 2497 * power(2,64) + 172 
  GROUP BY s1.person_id 
) m ON s.person_id = m.person_id AND 
       s.run_number * power(2,64) + s.transaction_id = m.CEILING_ID 
WHERE s.person_id = 'L1001001' AND 
      s.status != '2' AND 
      s.MESSAGE_SEQUENCE = m.MSG_SEQUENCE

谢谢

标签： sqloracle

解决方案

如果transaction_id总是小于POWER(2,64)那么您似乎正在找到message_sequence最近的最大值run_number，transaction_id并且您可以使用分析函数将其编写为：

甲骨文设置：

CREATE TABLE persons ( person_id, run_number, transaction_id, message_sequence, status ) AS
SELECT 'L1001001', 1, 1, 1, 1 FROM DUAL UNION ALL
SELECT 'L1001001', 1, 2, 2, 1 FROM DUAL UNION ALL
SELECT 'L1001001', 2, 1, 3, 1 FROM DUAL UNION ALL
SELECT 'L1001001', 2, 2, 5, 1 FROM DUAL UNION ALL
SELECT 'L1001001', 2, 3, 4, 1 FROM DUAL;

查询：

SELECT *
FROM   (
  SELECT p.*,
         MAX( message_sequence )
           KEEP ( DENSE_RANK LAST ORDER BY run_number, transaction_id )
           OVER ( PARTITION BY person_id )
           AS message_for_max_rn_ti,
         MAX( message_sequence )
           OVER ( PARTITION BY person_id )
           AS max_message_sequence
  FROM   persons p
  WHERE  person_id = 'L1001001'
  AND    (  run_number < 2497
         OR ( run_number = 2497 AND transaction_id < 172 ) )
)
WHERE message_sequence = max_message_sequence
AND   status != 2;

输出：

PERSON_ID | RUN_NUMBER | TRANSACTION_ID | 消息序列 | 状态 | MESSAGE_FOR_MAX_RN_TI | MAX_MESSAGE_SEQUENCE
:-------- | ---------: | -------------: | ---------------: | -----: | --------------------: | ------------------:
L1001001 | 2 | 2 | 5 | 1 | 4 | 5

db<>在这里摆弄

推荐阅读