postgres中执行DELETE … LEFT/RIGHT JOIN

问题:有两张表 users, addrs,需要从users表中删除一些行,条件为在“addrs”表中没有对应id的行。

table-users

table-users

table-addrs

table-addrs

事实上很简单,只是个left join查询,SELECT a.*,'|' s,b.* FROM users a LEFT JOIN addrs b ON a.id=b.id;,预览如下:

overview-join

overview-join

然而,postgres本身不支持 DELETE … LEFT JOIN … 这样的语句,这点不像MySQL那样方便。

有一个曲线救国方法:

把上面LEFT JOIN的结果表视为一张中间表,把原users表、按主键 inner join 到中中间表,然后从users表中按删除指定行。语句如下

DELETE FROM users
USING users a LEFT JOIN addrs b ON a.id=b.id
WHERE users.id=a.id AND b.name IS NULL;

问题得解。

当然也可以使用子查询,从中间表中取出id主键,以子查询形式删除。不推荐。

前文用表及数据

CREATE TABLE users (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    name character varying(255)
);
ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (id);

CREATE TABLE addrs (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    addr character varying(255)
);
ALTER TABLE addrs ADD CONSTRAINT addrs_pkey PRIMARY KEY (id);

INSERT INTO "users" VALUES (1, 'Tom');
INSERT INTO "users" VALUES (2, 'Jarry');
INSERT INTO "users" VALUES (3, 'Alias');
INSERT INTO "users" VALUES (4, 'Bob');
INSERT INTO "users" VALUES (5, 'somebody');

INSERT INTO "addrs" VALUES (1, 'Luoyang');
INSERT INTO "addrs" VALUES (2, 'Shanghai');
INSERT INTO "addrs" VALUES (3, 'Hongkong');

EOF.

 

mysql多表join联查语句错误:#1241 – Operand should contain 1 column(s)

对ecshop系统做一些改造,需要一个三表join的查询,但写出语句却报错了

#1241 – Operand should contain 1 column(s)

语句如下:

SELECT a . * , IFNULL( g.goods_thumb, ” ) AS goods_thumb, g.goods_name AS goods_name_from_goods, g.brand_id, b.brand_name
FROM `eb`.`ec_goods_activity` AS a
LEFT JOIN (`eb`.`ec_goods` AS g , `eb`.`ec_brand` AS b )
ON (a.goods_id = g.goods_id , b.brand_id = g.brand_id)
WHERE a.act_type = ‘5’
AND a.start_time <= ‘1333010119’
AND a.end_time >= ‘1333010119’
AND a.is_finished <2
ORDER BY a.act_id DESC
LIMIT 20

起初死活找不到错误,突然发现ON子句里把条件and错写成逗号了,改正后执行正确,问题解决。

SELECT a. * , IFNULL( g.goods_thumb, ” ) AS goods_thumb, g.goods_name AS goods_name_from_goods, g.brand_id, b.brand_name
FROM `eb`.`ec_goods_activity` AS a
LEFT JOIN (
`eb`.`ec_goods` AS g, `eb`.`ec_brand` AS b
) ON ( a.goods_id = g.goods_id AND b.brand_id = g.brand_id )
WHERE a.act_type = ‘5’
AND a.start_time = ‘1333010119’
AND a.is_finished ORDER BY a.act_id DESC
LIMIT 20