我有两个名为’tbl_users’和’tbl_order’的表,其中tbl_users有三列’id’,‘name’,‘items’,其中列’items’数据类型是数组,‘tbl_orders’有两列’id’,’ …
PostgreSQL支持您可以使用的数组运算符 解决 各自订单商品的标识符:
SELECT u.id, u.name, CASE WHEN max(o.item) IS NULL THEN NULL ELSE array_agg(o.item) END AS items FROM tbl_users AS u LEFT JOIN tbl_orders AS o ON (u.items @> array[o.id]) GROUP BY 1, 2;
在线演示: https://rextester.com/CZDC2260
......会回来:
id | name | items ----+------+------- 1 | john | {A,B} 2 | kate | {A} 3 | sue | 4 | ted | (4 rows)