树形结构类型主表查询子表

• MySQL写法

DROP TABLE IF EXISTS be_tag;
CREATE TABLE be_tag(
    `tag_id` bigint(19) NOT NULL  COMMENT '物理主键' ,
    `category_id` bigint(19) NOT NULL DEFAULT 0 COMMENT '标签类型ID' ,
    `tag_name` VARCHAR(255) NOT NULL  COMMENT '标签名称' ,
    `tag_en` VARCHAR(255) NOT NULL DEFAULT 1 COMMENT '英文名称' ,
    `owner` VARCHAR(255) NOT NULL  COMMENT '负责人' ,
    `create_by` VARCHAR(32)   COMMENT '创建人' ,
    `create_by_id` VARCHAR(32) NOT NULL  COMMENT '创建人id' ,
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,
    `update_by` VARCHAR(32)   COMMENT '更新人' ,
    `update_by_id` VARCHAR(32)   COMMENT '更新人id' ,
    `update_time` DATETIME  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ,
    `remark` VARCHAR(1000)   COMMENT '描述' ,
    PRIMARY KEY (tag_id)
)  COMMENT = '标签';

DROP TABLE IF EXISTS be_tag_category;
CREATE TABLE be_tag_category(
    `category_id` bigint(19) NOT NULL  COMMENT '物理主键' ,
    `parent_id` bigint(19) NOT NULL DEFAULT 0 COMMENT '父目录ID' ,
    `category_name` VARCHAR(100) NOT NULL  COMMENT '分类名称' ,
    `levels` tinyint(4) NOT NULL DEFAULT 1 COMMENT '层级' ,
    `ancestors` VARCHAR(255) NOT NULL  COMMENT '祖级列表' ,
    `create_by` VARCHAR(32)   COMMENT '创建人' ,
    `create_by_id` VARCHAR(32) NOT NULL  COMMENT '创建人id' ,
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,
    `update_by` VARCHAR(32)   COMMENT '更新人' ,
    `update_by_id` VARCHAR(32)   COMMENT '更新人id' ,
    `update_time` DATETIME  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ,
    `remark` VARCHAR(1000)   COMMENT '描述' ,
    PRIMARY KEY (category_id)
)  COMMENT = '标签分类';

select t.tag_id, t.category_id, t.tag_name, t.tag_en, t.create_time, t.create_by_id, t.update_time, t.remark, t.owner, u.nick_name as ownerName, u2.nick_name as createNickName
        from be_tag t left join (select c.ancestors,c.category_id from be_tag_category c union select '0' as ancestors, '0' category_id from dual) tc on t.category_id = tc.category_id
left join sys_user u on t.owner = u.user_id
left join sys_user u2 on t.create_by_id = u2.user_id
<where>
    (find_in_set(#{t.categoryId}, tc.ancestors) or tc.category_id = #{t.categoryId})
    <if test="t.tagName != null and t.tagName != ''">
        and (t.tag_name like concat('%', #{t.tagName}, '%') or t.tag_en like concat('%', #{t.tagName}, '%'))
    </if>
    <if test="t.owner != null and t.owner != ''">
        and t.owner = #{t.owner}
    </if>
</where>
order by t.update_time desc

• Oracle写法

CREATE TABLE be_tag(
    tag_id VARCHAR2(32) NOT NULL,
    category_id VARCHAR2(32) DEFAULT  0 NOT NULL,
    tag_name VARCHAR2(255) NOT NULL,
    tag_en VARCHAR2(255) DEFAULT  1 NOT NULL,
    owner VARCHAR2(255) NOT NULL,
    create_by VARCHAR2(32),
    create_by_id VARCHAR2(32) NOT NULL,
    create_time TIMESTAMP NOT NULL,
    update_by VARCHAR2(32),
    update_by_id VARCHAR2(32),
    update_time TIMESTAMP,
    remark VARCHAR2(1000),
    PRIMARY KEY (tag_id)
);

COMMENT ON TABLE be_tag IS '标签';
COMMENT ON COLUMN be_tag.tag_id IS '物理主键';
COMMENT ON COLUMN be_tag.category_id IS '标签类型ID';
COMMENT ON COLUMN be_tag.tag_name IS '标签名称';
COMMENT ON COLUMN be_tag.tag_en IS '英文名称';
COMMENT ON COLUMN be_tag.owner IS '负责人';
COMMENT ON COLUMN be_tag.create_by IS '创建人';
COMMENT ON COLUMN be_tag.create_by_id IS '创建人id';
COMMENT ON COLUMN be_tag.create_time IS '创建时间';
COMMENT ON COLUMN be_tag.update_by IS '更新人';
COMMENT ON COLUMN be_tag.update_by_id IS '更新人id';
COMMENT ON COLUMN be_tag.update_time IS '更新时间';
COMMENT ON COLUMN be_tag.remark IS '描述';

CREATE TABLE BE_TAG_CATEGORY
   (	"CATEGORY_ID" VARCHAR2(32) NOT NULL ENABLE, 
	"PARENT_ID" VARCHAR2(32) DEFAULT '0 ' NOT NULL ENABLE, 
	"CATEGORY_NAME" VARCHAR2(100) NOT NULL ENABLE, 
	"LEVELS" NUMBER(4,0) DEFAULT 1 NOT NULL ENABLE, 
	"ANCESTORS" VARCHAR2(255) NOT NULL ENABLE, 
	"CREATE_BY" VARCHAR2(32), 
	"CREATE_BY_ID" VARCHAR2(32) NOT NULL ENABLE, 
	"CREATE_TIME" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATE_BY" VARCHAR2(32), 
	"UPDATE_BY_ID" VARCHAR2(32), 
	"UPDATE_TIME" TIMESTAMP (6), 
	"REMARK" VARCHAR2(1000), 
	 PRIMARY KEY ("CATEGORY_ID")
   );

COMMENT ON TABLE SIPDM3.BE_TAG_CATEGORY IS '标签分类';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.CATEGORY_ID IS '物理主键';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.PARENT_ID IS '父目录ID';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.CATEGORY_NAME IS '分类名称';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.LEVELS IS '层级';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.ANCESTORS IS '祖级列表';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.CREATE_BY IS '创建人';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.CREATE_BY_ID IS '创建人id';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.UPDATE_BY IS '更新人';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.UPDATE_BY_ID IS '更新人id';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN SIPDM3.BE_TAG_CATEGORY.REMARK IS '描述';


   select t.tag_id, t.category_id, t.tag_name, t.tag_en, t.create_time, t.create_by_id, t.update_time, t.remark, t.owner
        from be_tag t left join (select c.ancestors,c.category_id from be_tag_category c union select '0' as ancestors, '0' category_id from dual) tc on t.category_id = tc.category_id 
        WHERE (INSTR(',' || tc.ancestors || ',', ',' || '2.1' || ',') > 0 OR tc.CATEGORY_ID = '2.1')