我的数据库中有三个表。
表1结构：
date, family, class2, sec2, core1, nlm
表2结构：
date, family, class2, sec2, core2, prefix
表3结构：
date, family, class3, sec3, core3, root
我想创建一个结合这三个表的视图。视图将具有如下结构：
date, class, sec, core, group_of_family
类在表1中以类1、表3中以类2和表3中的类3的形式存在。秒和核心也是如此。
在上述数据中，一组日期、类别、秒和核心可以是不同家族的一部分，如：
表1结构：
date, family, class2, sec2, core1, nlm
22/7/2020, f1, r1, p1, xyz, pqr
22/7/2020 f2, r1, p1, xyz, pqrs
24/7/2020 f5, r2, p4, sds, dsg
表2结构：
date, family, class2, sec2, core1, nlm
22/7/2020, f4, r1, p1, xyz, pqr
24/7/2020 f8, r6, p1, fds, sdg
表3结构：
date, family, class2, sec2, core1, nlm
28/7/2020, f14, r1, p1, xyz, pqr
24/7/2020 f18, r6, p1, fds, sdg
所以我想要的视图应该是这样的：
date, class, sec, core, group_of_family
22/7/2020, r1, p1, xyz, f1,f2,f4
24/7/2020, r2, p4, sds, f5
24/7/2020, r6, p1, fds, f8,f18
28/7/2020, r1, p1, xyz, f14
我是新的Postgresqldb，在编写复杂查询方面没有太多经验。
有人能帮我查询一下吗？谢谢
所以给定的数据是：
create table tbl1
(
date date,
family text,
class2 text,
sec2 text,
core1 text,
nlm text
);
insert into tbl1 values
('2020-07-22','f1','r1','p1','xyz','pqr'),
('2020-07-22','f2','r1','p1','xyz','pqrs'),
('2020-07-24','f5','r2','p4','sds','dsg');
create table tbl2
(
date date,
family text,
class2 text,
sec2 text,
core1 text,
nlm text
);
insert into tbl2 values
('2020-07-22','f4','r1','p1','xyz','pqr'),
('2020-07-24','f8','r6','p1','fds','sdg');
create table tbl3
(
date date,
family text,
class2 text,
sec2 text,
core1 text,
nlm text
);
insert into tbl3 values
('2020-07-28','f14','r1','p1','xyz','pqr'),
('2020-07-24','f18','r6','p1','fds','sdg');
查询：
create view vw_test as
select date,class,sec,core,string_agg(family,',') as group_of_family
from
(
select date,class2 as class,
sec2 as sec,
core1 as core,
family
from tbl1
union
select date,class2 as class,
sec2 as sec,
core1 as core,
family
from tbl2
union
select date,class2 as class,
sec2 as sec,
core1 as core,
family
from tbl3
)a
group by date,class,sec,core;
检查预期结果：
select * from vw_test;
您必须使用string_agg()函数，
select date, class2 as class, sec2 as ssec, core1 as core, string_agg(col2,' , ' order by class, sec) as group_of_family from
(select date, family, class2, sec2, core1 from table1
union all
select date, family, class2, sec2, core1 from table2
union all
select date, family, class2, sec2, core1 from table3) qry
group by date, class2, sec2, core1;