多表关联查询

gg22g2
  • 122

现在有三张表,人(person),机构(organization),上下级关系(rel),

create table organization
(
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  PRIMARY KEY (`id`)
);

create table person
(
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE rel  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `from_id` int UNSIGNED NULL COMMENT '上级id',
  `from_type` varchar(5) NULL COMMENT '上级类型',
  `to_id` int UNSIGNED NULL COMMENT '下级id',
  `to_type` varchar(5) NULL COMMENT '下级类型',
  PRIMARY KEY (`id`)
);



insert into person(name) value('王总'), ('王经理') , ('马经理'), ('楼经理');

insert into organization(name) value('集团1'), ('集团2') , ('集团3');

--psn代表这个id是person表中的数据,org代表这个id是organization表中的数据
insert into rel(from_id,from_type,to_id,to_type) values(1,'psn',1,'org');
insert into rel(from_id,from_type,to_id,to_type) values(1,'psn',2,'org');
insert into rel(from_id,from_type,to_id,to_type) values(1,'psn',3,'org');
insert into rel(from_id,from_type,to_id,to_type) values(2,'org',2,'psn');
insert into rel(from_id,from_type,to_id,to_type) values(3,'org',3,'psn');
insert into rel(from_id,from_type,to_id,to_type) values(4,'org',4,'psn');

人和机构,他们之间有上下级关系,比如某个人控股某个公司,某个公司下有某个员工等,所有用rel表关联起来。

现在需求是查询rel中所有记录,但是返回的数据要返回上级和下级的名字,类似(select from_id, from_name as 上级名称,to_id,to_name as 下级名称 from .......)

我目前试过左连接四次来获取名字,或者用三条sql,第一条查rel表,再java程序中提取所有from_id和to_id,根据类型,放入两个List中,再查一次person表,一次organization表来获取名称。

请问是否有其他方式来实现

回复
阅读 494
2 个回答

personorganization 合并在一起,再去和 rel 连。

with ent as (
  select id, name, 'psn' as ent_type from person
  union all
  select id, name, 'org' as ent_type from organization)
select from_id, f.name as 上级名称, to_id, t.name as 下级名称
from rel left join ent as f on rel.from_id = f.id and rel.from_type = f.ent_type
  left join ent as t on rel.from_id = t.id and rel.from_type = t.ent_type

人和机构2个表,数据量应该不是很大,可以放到缓存里

直接读取关系表,代码层面再数据加工

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
你知道吗?

宣传栏