生成阈值oracle union 或者 union all 与 order by 的联合使用

1、将排序后的查询结果拼接起来
select * from(select  *  from table order by a)

union

select * from (select * from table1 order by b)

union

select * from (select * from table2 order by c);

 

分页查询格式1
在查询的最外层控制分页的最小值和最大值。查询语句如下： 
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40

 

1. select to_char('aaa')from dual;--aaa

2. select '' || to_char('aaa') || ''from dual;--aaa

3. select '''' || to_char('aaa') || '''' from dual;--'aaa'

4. select '''''' || to_char('aaa') || '''''' from dual;--''aaa''

5. select '''''''' || to_char('aaa') || '''''''' from dual;--'''aaa'''

6. select ' '' ' ||' ' || ' '' ' || to_char('aaa') || ' '' '' ' from dual;-- '

 

  下面是我的sql

标题

     实际结果

     

 

select * from 
	(
		select a.*, rownum*2+1 as rk from 
			(
				select '{"field1":"' || fieldname1 || '",' || '"field2":"' || fieldname2 || '}' as tt
				from table
				where 
				order by 
			) a 
		union all
		select b.*, rownum*2 as rk from 
			(
				select '{"field1":"' || fieldname1 || '",' || '"field2":"' || fieldname2 || '}' as tt
				from table
				where 
				order by 
			) b 
	)
order by rk;