在pgAdmin3中,要建立一个多表联合查询的复杂视图,查阅很多资料和实验,总算是把视图做出来了
首先,我们来看一下简单的查询结果是怎么样的。
简单的多表查询语句如下:
-- View: public."V_ZHJG_GRID"
-- DROP VIEW public."V_ZHJG_GRID";
CREATE OR REPLACE VIEW public."V_ZHJG_GRID" AS
SELECT a."ID",
a."GRIDID",
a."GRIDNAME",
a."TYPE",
a."XZQ",
a."PQID",
a."PQNAME",
a."BBOX",
a.the_geom,
u."ID" AS "USERID",
u."TRUENAME" AS "USERNAME",
u."PHONE"
FROM "ZHJG_GRID_ATTRIBUTE" a
LEFT JOIN "ZHJG_GRID_USER" ue ON a."ID"::text = ue."GRIDATTRIBUTEID"::text
LEFT JOIN "KTW_UC_USER" u ON u."ID"::text = ue."USERID"::text;
ALTER TABLE public."V_ZHJG_GRID"
OWNER TO postgres;
COMMENT ON VIEW public."V_ZHJG_GRID"
IS '网格属性信息';
查询结果如下:
显然这样的结果并不是我所需要的,我需要的是一条数据,将后面的人员信息组合成一个json数组格式
为满足条件,我首先要做的就是需要将后面查询出来的用户信息,拼接成一个json格式,使用concat将结果拼接成json,第二步,我需要将单个的json拼接成json数组,这样方便后面获取,同时需要把数据合并成一条。
使用string_agg合并json,concat完善json成数组,通过group by将多条数据合并成一条。
最终的sql语句如下:
-- View: public."V_ZHJG_GRIDANDUSER"
-- DROP VIEW public."V_ZHJG_GRIDANDUSER";
CREATE OR REPLACE VIEW public."V_ZHJG_GRIDANDUSER" AS
SELECT n."ID",
n."GRIDID",
n."GRIDNAME",
n."TYPE",
n."XZQ",
n."PQID",
n.gid,
n."PQNAME",
n."BBOX",
n.the_geom,
concat('[', string_agg(m."user", ','::text), ']') AS "user"
FROM
( SELECT a."ID",
a."GRIDID",
a."GRIDNAME",
a."TYPE",
a."XZQ",
a."PQID",
a."PQID" AS gid,
a."PQNAME",
a."BBOX",
a.the_geom,
ue."USERID"
FROM "ZHJG_GRID_ATTRIBUTE" a
LEFT JOIN "ZHJG_GRID_USER" ue ON a."ID"::text = ue."GRIDATTRIBUTEID"::text) n
LEFT JOIN
( SELECT
u."ID" AS "USERIDS",
concat('{"userId":"', u."ID", '","userName":"', u."TRUENAME", '","phone":"',
u."PHONE", '","orgId":"', u."ORGID", '","postId":"', p."ID", '","postName":"',
p."NAME", '"}') AS "user"
FROM "KTW_UC_USER" u
LEFT JOIN "KTW_UC_POST_USER" pu ON u."ID"::text = pu."USERID"::text
LEFT JOIN "KTW_UC_POST" p ON pu."POSTID"::text = p."ID"::text) m
ON n."USERID"::text = m."USERIDS"::text
GROUP BY n."ID", n."GRIDID", n."GRIDNAME", n."TYPE", n."XZQ", n."PQID", n.gid, n."PQNAME", n."BBOX", n.the_geom;
ALTER TABLE public."V_ZHJG_GRIDANDUSER"
OWNER TO postgres;
COMMENT ON VIEW public."V_ZHJG_GRIDANDUSER"
IS '网格人员属性视图';
查询结果如下:
user的格式:
[{"userId":"kf8be17e1dd8448218e9ac78460015d2","userName":"xcy01","phone":"18407395922","orgId":"k547db647d4444233a63421ecc865495","postId":"kf0322b1b35854e7ab43af4c70a083cf","postName":"lwlk巡查员"},{"userId":"k4ce2af0d26ec42a09e403807eb32932","userName":"kcy01","phone":"18407395922","orgId":"k547db647d4444233a63421ecc865495","postId":"k40030a9ae16741ac88ab6a2d3cb02c6","postName":"lwlk勘测员"},{"userId":"ked42694b95c8443caaa2d723faa591d","userName":"责任人","phone":"18407395922","orgId":"k547db647d4444233a63421ecc865495","postId":"kace4ee2e1ca04ad2a94a7dca168f2c5","postName":"lwlk责任人"},{"userId":"ka418df5dbb9d4af48a0b17a437577e3","userName":"kcy02","phone":"18407395922","orgId":"k547db647d4444233a63421ecc865495","postId":"k40030a9ae16741ac88ab6a2d3cb02c6","postName":"lwlk勘测员"}]
好了,想要的结果也出来了,过程还是比较艰辛的,哈哈,记录下来,防止后面忘记了