MySQL递归自连接 [英] mysql recursive self join

问题描述

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)

我可以使用单个自连接找出'X'下包含的所有distinct值吗?

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

EDIT

就像这里 X包含A，B和C A包含P P包含Q Q包含R R包含Y Y包含C，D和E ...

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...

所以我想在查询X时显示A，B，C，D，E，P，Q，R，Y.

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<String> fetched;
    private List<String> fresh;

    public List<String> findAllContainedNMByMarketId(Connection conn, String marketId) throws SQLException{
        fetched = new ArrayList<String>();
        fresh = new ArrayList<String>();
        fresh.add(marketId.toLowerCase());
        while(fresh.size()>0){
            fetched.add(fresh.get(0).toLowerCase());
            fresh.remove(0);
            List<String> tempList = Nm2NmFactory.getContainedNmByContainerNm(conn, fetched.get(fetched.size()-1));
            if(tempList!=null){
                for(int i=0;i<tempList.size();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.

推荐答案

您不能使用具有该数据结构的单个联接来递归地获取所有包含的对象.您将需要一个递归查询，但是MySQL尚不支持.

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.

这篇关于MySQL递归自连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！