Mysql Question posted in
The official MySQL documentation can be found here.

Query chain data in MySQL

I have a table sample below:
Table_A

col1 col2
881 113
988 899
113 765
765 765
122 881
300 400
765 910
910 345
999 988

What I want to achieve here, is to get the last chaining data based on table above with the requirement as the following, each data must find their chain data based on col2 to col1 until the data not find any chain and ignore the data with the same value for example in this case for the col1 = 122 and col2 = 881 should be 122>881>113>765>910>365. For the data that does not have any data chain or data with the same value could be leave it in blank or null.

I try to do self-join, but it only accommodates 1 time chain only. I am looking for dynamic chain based on the number of data itself.

SELECT A.col1,
       A.col2,
       B.col1
FROM Table_A AS A
LEFT JOIN Table_A AS B

The expecting result as follow:

col1 col2 last_chain_data
881 113 345
988 899 NULL
113 765 345
765 765 NULL
122 881 345
300 400 NULL
765 910 345
910 345 NULL
999 988 899

is there any method to achieve this in MySQL?

Tags:

2

Answers


  1. 0

    Use recursive CTE:

    WITH RECURSIVE
cte1 AS (
  SELECT col1, col2, CAST(col2 AS CHAR(65535)) path, 1 level
  FROM test
  UNION ALL
  SELECT cte1.col1, cte1.col2, CONCAT_WS(',', cte1.path, test.col2), cte1.level + 1
  FROM test
  JOIN cte1 ON FIND_IN_SET(test.col1, cte1.path)
  WHERE NOT FIND_IN_SET(test.col2, cte1.path)
  ),
cte2 AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY LENGTH(path) DESC) rn
  FROM cte1
  )
SELECT col1, col2, SUBSTRING_INDEX(path, ',', -1) last_chain_data
FROM cte2 
WHERE rn = 1
ORDER BY last_chain_data, level DESC;
    col1 col2 last_chain_data
    122 881 345
    881 113 345
    113 765 345
    765 765 345
    765 910 345
    910 345 345
    300 400 400
    999 988 899
    988 899 899

    fiddle

    Login or Signup to reply.
  2. 0

    Another recursive query example

    with RECURSIVE trn as(select *,row_number()over(order by col1,col2) rn from test)
, r as(
  select 0 lvl,t1.rn,t1.col1,t1.col2,t2.col2 next, t2.rn next_rn
    ,concat(':',t1.rn,':',t2.rn) path
  from trn t1 left join trn t2
     on t2.col1=t1.col2 and t1.rn<>t2.rn
  union all
  select lvl+1 lvl,r.rn,r.col1,r.col2,t.col2 next,t.rn 
    ,concat(path,':',t.rn) path
  from r left  join trn t
     on t.col1=r.next and t.rn<>r.rn
  where lvl<7 and locate(t.rn,path)=0
)
select *
  from(
  select * 
     ,row_number()over(partition by rn order by lvl desc)rrn
  from r
  ) x
where rrn=1;

    Output is

    lvl rn col1 col2 next path
    2 1 113 765 345 :1:4:5:7
    4 2 122 881 345 :2:6:1:4:5:7
    0 3 300 400 null null
    1 4 765 765 345 :4:5:7
    0 5 765 910 345 :5:7
    3 6 881 113 345 :6:1:4:5:7
    0 7 910 345 null null
    0 8 988 899 null null
    0 9 999 988 899 :9:8

    About (765, 765)–>null – why the result should be like this should be further described in the question. I don’t want to guess. In this case, the result may be ambiguous.

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search