mysql 单标递归,MySQL递归自连接

create table test(

container varchar(1),

contained varchar(1)

);

insert into test values('X','A');

insert into test values('X','B');

insert into test values('X','C');

insert into test values('Y','D');

insert into test values('Y','E');

insert into test values('Y','F');

insert into test values('A','P');

insert into test values('P','Q');

insert into test values('Q','R');

insert into test values('R','Y');

insert into test values('Y','X');

select * from test;

mysql> select * from test;

+-----------+-----------+

| container | contained |

+-----------+-----------+

| X | A |

| X | B |

| X | C |

| Y | D |

| Y | E |

| Y | F |

| A | P |

| P | Q |

| Q | R |

| R | Y |

| Y | X |

+-----------+-----------+

11 rows in set (0.00 sec)

Can I find out all the distinct values contained under 'X' using a single self join?

EDIT

Like, Here

X contains A, B and C

A contains P

P contains Q

Q contains R

R contains Y

Y contains C, D and E...

So I want to display A,B,C,D,E,P,Q,R,Y when I query for X.

EDIT

Got it right by programming.

package com.catgen.helper;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.catgen.factories.Nm2NmFactory;

public class Nm2NmHelper {

private List fetched;

private List fresh;

public List findAllContainedNMByMarketId(Connection conn, String marketId) throws SQLException{

fetched = new ArrayList();

fresh = new ArrayList();

fresh.add(marketId.toLowerCase());

while(fresh.size()>0){

fetched.add(fresh.get(0).toLowerCase());

fresh.remove(0);

List tempList = Nm2NmFactory.getContainedNmByContainerNm(conn, fetched.get(fetched.size()-1));

if(tempList!=null){

for(int i=0;i

String current = tempList.get(i).toLowerCase();

if(!fetched.contains(current) && !fresh.contains(current)){

fresh.add(current);

}

}

}

}

return fetched;

}

}

Not the same table and fields though. But I hope you get the concept.

Thanks guys.

解决方案

You can't get all the contained objects recursively using a single join with that data structure. You would need a recursive query but MySQL doesn't yet support that.

You could however construct a closure table, then you can do it with a simple query. See Bill Karwin's slideshow Models for heirarchical data for more details and other approaches (for example, nested sets). Slide 69 compares the different designs for ease of implementing 'Query subtree'. Your chosen design (adjacency list) is the most awkward of all four designs for this type of query.