单表多次 join如何实现

[tiandankanfeng]

table cate:

create table cate
(
    id          bigint       not null
        primary key,
    cate_name   varchar(200) null,
    description varchar(200) null,
    pid         bigint       null,
    rootCateId  bigint       null
);

if I want to get a sql like this:

select *, d.cate_name as 'root_cate_name' from (
    select a.id, a.cate_name, a.pid, b.cate_name parenrCateName, a.root_cate_id
       from cate a left join cate b on  a.pid = b.id
    ) as c left join cate d on c.root_cate_id = d.id;

I think it hard to achieve it.

public class CateVO implements Serializable {

    private Long id;
    private String cateName;
    private String description;
    private Long pid;
    private String parentName;
    private Long rootCateId;
    private String rootCateName;
}

[tiandankanfeng]

ok, I solve it.
@SearchBean(tables = "(select a.id, a.cate_name, a.pid, b.cate_name parentCateName, a.root_cate_id, a.description description"
+ " from cate a left join cate b on a.pid = b.id) as c, cate d",
where = "c.root_cate_id = d.id")
public class CateVO implements Serializable {

@DbField("c.id")
private Long id;

@DbField("c.cate_name")
private String cateName;

@DbField("c.description")
private String description;

@DbField("c.pid")
private Long pid;

@DbField("c.parentCateName")
private String parentName;

@DbField("c.root_cate_id")
private Long rootCateId;

@DbField("d.cate_name")
private String rootCateName;

}