标量子查询要避免单行子查询返回多行

点击(此处)折叠或打开

DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1(ID NUMBER, VALUE VARCHAR2(20));
CREATE TABLE t2(ID NUMBER, VALUE VARCHAR2(20));
INSERT INTO t1 VALUES (1, \'A\');
INSERT INTO t1 VALUES (2, \'D\');
INSERT INTO t1 VALUES (3, \'L\');
INSERT INTO t2 VALUES (1, \'B\');
INSERT INTO t2 VALUES (1, \'C\');
INSERT INTO t2 VALUES (2, \'K\');
COMMIT;
--子查询返回了多行,出错
SELECT t1.*, (SELECT ID FROM t2 WHERE t2.ID = t1.ID) FROM t1;
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
--用如下小技巧改造SQL, 判断出t1 表哪个记录对应到t2 表返回多行。
SELECT * FROM (SELECT t1.*, (SELECT COUNT(*) FROM T2 WHERE t2.ID = t1.ID) cnt FROM t1) WHERE cnt > 1;
ID VALUE CNT
---------- -------------------- ----------
1 A 2

ITPub博客

标量子查询（二）