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)

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

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

EDIT

就像这里X包含A，B和CA包含PP包含QQ包含RR包含YY包含C，D和E ...

Like, HereX contains A, B and CA contains PP contains QQ contains RR contains YY 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.

但是，您可以构造一个闭合表，然后可以通过一个简单的查询来完成它.参见比尔·卡温的幻灯片用于分层数据的模型，以了解更多详细信息和其他方法(例如，嵌套集).幻灯片69比较了不同的设计，以简化实现查询子树"的过程.对于这种类型的查询，您选择的设计(邻接列表)是所有四种设计中最笨拙的.

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递归自连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！