我有四个表作为表1
srno name cat main_srno
0001 prod1 A 0005
0002 prod2 B NULL
0003 prod6 Z 0002
0004 prod22 E 0005
0005 prod11 Y NULL
表2
srno date
0001 2018-01-01
0002 2018-01-02
0003 2018-02-01
0001 2018-01-02
0002 2018-01-02
表3
srno date
0001 2018-01-01
0002 2018-01-02
0003 2018-02-01
0001 2018-01-02
0002 2018-01-02
我想要这样的东西
SELECT A.*,
(SELECT string_agg(name,',') FROM table1
WHERE main_srno=A.srno
AND srno NOT IN (SELECT srno FROM table2
WHERE date='2017-01-02'
UNION
SELECT cino FROM table3
WHERE date='2017-01-02'
)
) AS child
FROM table1 AS A
WHERE A.srno NOT IN (SELECT srno FROM table2
WHERE date='2017-01-02'
UNION
SELECT cino FROM table3
WHERE date='2017-01-02'
);
输出应如下所示:
srno name cat child
0005 prod11 Y prod22,prod1
我如何以优化的方式执行此操作,因为可能有大量记录。
重写查询以使用 CTE 和自联接,并将 NOT IN
子句更改为两个NOT EXTSTS
子句:
WITH cte AS
(SELECT * FROM table1
WHERE NOT EXISTS (SELECT table2.srno FROM table2
WHERE table2.srno = a.srno
AND table2.date = '2017-01-02')
AND NOT EXISTS (SELECT table3.srno FROM table3
WHERE table3.srno = a.srno
AND table3.date = '2017-01-02')
)
SELECT a.*, string_agg(b.name,',')
FROM cte a
LEFT JOIN cte b ON a.srno = b.main_srno;
在支持反联接的表上添加索引(这取决于执行计划哪些是最好的(。