Question

Oracle数据库，如何通过SELECT查询固定行数的数据？

我想建一个视图，内部是一些类似如下的数据
col1 col2 col3
1 2 3
1 3 2
2 1 1
但是我现在想要的结果是这样的
col1 col2 col3
1 2 3
1 3 2
1

1

2 1 1
2
2
2

select出固定行数的数据，如果没有用空来补充，求大家帮帮忙啊！

Answer 1

可用rownum来查询。

如emp表中有如下数据：

现在要求只查5行数据，可用如下语句：

select * from emp where rownum<=5;

结果如下：

Answer 2

ROWNUM过滤
eg.select*from tab where rownum=n;

Answer 3

with x as
(select '1' col1, '2' col2, '3' col3
from dual
union
select '1' col1, '3' col2, '2' col3
from dual
union
select '2' col1, '1' col2, '1' col3
from dual)
select *
from (select *
from x
union all
select i.col1, null, null
from (select col1, 4 - count(1) cn from x group by col1) i
connect by level <= i.cn
group by i.col1, level)
order by col1, col2, col3

Answer 4

with x as
(select '1' col1, '2' col2, '3' col3
from dual
union
select '1' col1, '3' col2, '2' col3
from dual
union
select '2' col1, '1' col2, '1' col3
from dual)
select *
from (select *
from x
union all
select i.col1, null, null
from (select col1, 4 - count(1) cn from x group by col1) i
connect by level <= i.cn
group by i.col1, level)
order by col1, col2, col3

Answer 5

看不懂,你的固定行有什么判断条件没有?或者什么?

追问

比如说现在col1列为1的是2条数据，col1为2的是1条数据，我想要col1为1的4条数据，另外两条空着；col1为2的4条数据，其他3条空着。