Oracle SQL查询需求：筛选值差≥5的相邻位置记录

Step-by-Step Breakdown #

First, we need to translate the positionid into numerical values we can use to check adjacency. Here's the core logic:

• Split positionid into its alphabetical and numeric components (e.g., turn B02 into letter B and number 2).
• Convert the letter to a number (A=1, B=2, ..., K=11) using ASCII calculations, so we can measure how far apart two letters are.
• Convert the numeric suffix to an actual number to check row adjacency.
• Use a self-join to compare every position against every other position, filtering for 3x3 grid neighbors and valid value differences.
• Add a check to avoid duplicate pairs (like (A01, B02) and (B02, A01)) to keep results clean.

Complete Oracle SQL Query #

Replace your_table_name with the actual name of your table:

SELECT
    t1.positionid AS position_1,
    t1.value AS value_1,
    t2.positionid AS position_2,
    t2.value AS value_2
FROM
    your_table_name t1
JOIN
    your_table_name t2 ON
    -- Check letter adjacency (max 1 letter apart)
    ABS(ASCII(SUBSTR(t1.positionid, 1, 1)) - ASCII(SUBSTR(t2.positionid, 1, 1))) <= 1
    AND
    -- Check number adjacency (max 1 number apart)
    ABS(TO_NUMBER(SUBSTR(t1.positionid, 2)) - TO_NUMBER(SUBSTR(t2.positionid, 2))) <= 1
    AND
    -- Skip pairing a position with itself
    t1.positionid != t2.positionid
WHERE
    -- Ensure value difference meets the threshold
    ABS(t1.value - t2.value) >= 5
    -- Optional: Prevent duplicate pairs by only keeping ordered pairs
    AND t1.positionid < t2.positionid
ORDER BY
    t1.positionid, t2.positionid;

Regex for PositionID Validation/Splitting #

If you need to validate or parse positionid with regex, here's a pattern that matches your format (A-K followed by 01-25):
^([A-K])(0[1-9]|1[0-9]|2[0-5])$

• ^([A-K]) captures the letter component (A through K)
• (0[1-9]|1[0-9]|2[0-5]) captures the numeric suffix (01 to 25)

This regex works with Oracle's REGEXP_SUBSTR function, but the SUBSTR + ASCII/TO_NUMBER approach used in the query is more efficient for adjacency checks.

内容的提问来源于stack exchange，提问作者lpmbl