CREATE TABLE table1
(
id bigint NOT NULL,
name character varying(50),
CONSTRAINT table1_pkey PRIMARY KEY (id )
)
WITH (
OIDS=FALSE
);
CREATE TABLE tablesub1
(
dbid bigint NOT NULL,
reftableid bigint,
name character varying(50),
value character varying(50),
CONSTRAINT tablesub1_pkey PRIMARY KEY (dbid ),
CONSTRAINT tablesub1_reftableid_fkey FOREIGN KEY (reftableid)
REFERENCES table1 (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
table1
id name
1;"jack"
2;"jack1"
3;"jack2"
4;"jack3"
tablesub1
dbid refertableid name value
1;1;"p_task_name";"a"
2;1;"due_date";"b"3;2;"p_task_name";"c"
4;2;"due_date";"d"
SELECT mainTable.id,mainTable.name, name3,value3, name4,value5
FROM (
SELECT id,name,name2 as name3 ,value as value3
FROM (
SELECT a1.*,a2.name as name2, a2.value as value
FROM table1 a1
LEFT JOIN tablesub1 a2 ON a1.id=a2.reftableid
) a
WHERE a.name2 = 'p_task_name'
) mainTable
LEFT JOIN (
SELECT id,name,name2 AS name4 ,value AS value5
FROM (
SELECT b1.*,b2.name AS name2, b2.value AS value
FROM table1 b1
LEFT JOIN tablesub1 b2 ON b1.id=b2.reftableid
) b
WHERE b.name2 = 'due_date'
) leftjoinTable
ON mainTable.id = leftjoinTable.id
结果
1;"jack";"p_task_name";"a";"due_date";"b"
2;"jack1";"p_task_name";"c";"due_date";"d"