select * from t_user where username ='admin' union select * from t_user where username ='admin'
SELECT * FROM (select * from t_user where username ='admin' ) CONNECT BY ROWNUM <3
想要多少条就修改:ROWNUM <3中的数字
select * from t_user where username in('admin','admin') union all select * from t_user where username in('admin','admin');
没明白我的意思,我还想要其它的记录呢,如select * from t_user where username in('admin','admin','test','test1'); 这样我想返回四条记录,怎么弄
select * from t_user where username in ('1','2','1') connect by rownum<=(length(',1,2,1,')-length(replace(',1,2,1,',','||username||',','')))/length(','||username||',')
select * from t_user where username ='admin'
union
select * from t_user where username ='admin'
union all select * from t_user where username in('admin','admin');
from t_user
where username in ('1','2','1')
connect by rownum<=(length(',1,2,1,')-length(replace(',1,2,1,',','||username||',','')))/length(','||username||',')
收藏!Robin_Ares写的SQL真XXX的出神入化!!!不得不使用感叹词。
但是?是多少需要自己算
我的想法是 (字符串长度-字符串replace指定字段的长度)/指定字段的长度,就是指定字段有多少个
加那么多','是因为无法区分1和11这样的长度思路是这样的,要是有问题的话你可以自己改一下
with TEST_ABC as (
select '1,2,2' b from dual
)
SELECT SUBSTR(',' || B || ',',
INSTR(',' || B || ',', ',', 1, RN) + 1,
INSTR(',' || B || ',', ',', 1, RN + 1) -
INSTR(',' || B || ',', ',', 1, RN) - 1) "NEW_B"
FROM TEST_ABC,
(SELECT ROWNUM RN
FROM ALL_OBJECTS
WHERE ROWNUM <= (SELECT MAX(LENGTH(B) - LENGTH(REPLACE(B, ',', '')))
FROM TEST_ABC) + 1) OB
WHERE INSTR(B || ',', ',', 1, RN) > 0;结果
new_b
1
2
2然后和你的表直连即可