请教一个子查询的问题

显示全部楼层 · 2012-5-21 10:19:41

SELECT c1,

(SELECT 1 FROM dual)c2
FROM table1;
可以运行

那么怎么写才能运行
SELECT c1,

(SELECT 1,2 FROM dual)(c2,c3)
FROM table1;

这个不要
SELECT c1,

(SELECT 1 FROM dual)c2,

(SELECT 2 FROM dual)c3
FROM table1;

千问 · 2012-5-21 10:19:41

SELECT c1,

c2,c3
FROM table1,(SELECT 1 c1,2 c2 FROM dual) ;

千问 · 2012-5-21 10:19:41

实际应用可能没这么简单，子查询往往是根据父查询的列来作查询条件。楼主的需求可先把多列合成一列，然后再套一层解析出来。
SELECT C1,SUBSTR(cols,1,1) AS C2,SUBSTR(cols,3,1) AS C3
FROM (
SELECT c1,
(SELECT '1'||','||'2' FROM dual) AS cols
FROM table1
)

千问 · 2012-5-21 10:19:41

bookmark

千问 · 2012-5-21 10:19:41

请修改下面这个：
SELECT c1,

(SELECT c2,c3 FROM table2 where c1 = table1.c1)(c2,c3)
FROM table1;

不要这个：
SELECT c1,

(SELECT c2 FROM table2 where c1 = table1.c1)c2,

(SELECT c3 FROM table2 where c1 = table1.c1)c3
FROM table1;

千问 · 2012-5-21 10:19:41

不是给你例子了吗？
我假设你C2的长度不超过20 (你可自己修改, 你可以用分隔符的方法)
SELECT C1
,TRIM(SUBSTR(C23,1,20)) AS C2 --- 原理就是要把C23拆开，你可以用其他方法
,SUBSTR(C23,21) AS C3
(SELECT c1,
(SELECT RPAD(c2,20)||c3 FROM table2 where c1 = table1.c1) AS C23
FROM table1
);

千问 · 2012-5-21 10:19:41

请修改下面这个语句，让它可以运行：
SELECT c1,

(SELECT c2,c3 FROM table2 where c1 = table1.c1)(c2,c3)
FROM table1;
这是我改的，还有没有别的？
SELECT c1,

(SELECT c2 FROM table2 where c1 = table1.c1)c2,

(SELECT c3 FROM table2 where c1 = table1.c1)c3
FROM table1;

千问 · 2012-5-21 10:19:41

SELECT table1.c1, table2.c2,table2.c3FROM table1,table2 where table1.c1 = table2.c1;

千问 · 2012-5-21 10:19:41

那么请修改下面这个，这个比较接近：
SELECT c1,
(SELECT c2, c3 FROM table2 WHERE c1 = table1.c1)(c2, c3),
(SELECT MAX(c4) FROM table4 WHERE c1 = table1.c1) c4,
(SELECT c5 FROM table5 WHERE c1 = table1.c1 AND ROWNUM = 1) c5
FROM table1;

千问 · 2012-5-21 10:19:41

没有排序，你的rownum是没有意义的
你先按8楼的思路写，出错了，再提问