有一个查询的难题，求高手解惑

[pgameli]

数据表如下:
表名：t
id kind tile content .......(其他字段)
1 1 fsdf dgfdgd
2 1 fsfd sdgfd
3 1 dfsa dfsdg
4 1 dfd gfdg
5 1 dsfa dsgf
6 1 fds dsfs
7 2 dsfs fdsfs
8 2 fsd fsds
9 3 fsdf dgfdgd
10 3 fsfd sdgfd
11 3 dfsa dfsdg
12 3 dfd gfdg
13 3 dsfa dsgf

以kind字段分组，只取5行记录，如果不满5条就用空行代替
最终实现这样的效果
id kind tile content .......(其他字段)
1 1 fsdf dgfdgd
2 1 fsfd sdgfd
3 1 dfsa dfsdg
4 1 dfd gfdg
5 1 dsfa dsgf
7 2 dsfs fdsfs
空行
空行
空行
空行
9 3 fsdf dgfdgd
10 3 fsfd sdgfd
11 3 dfsa dfsdg
12 3 dfd gfdg
13 3 dsfa dsgf
困扰一下午了，求高手解惑，到底应该怎么写这条SQL语句，谢谢

[pgameli]

没人了！！

[luoyoumou]

[Quote=引用 1 楼 pgameli 的回复:]
没人了！！
[/Quote]

-- 当有记录时：取最多5行的规则是什么啊？

-- 用 row_number() over(partition by ... order by) 呗！

[iihero]

SQL> select * from t;

ID KIND TITLE
---------- ---------- ----------
1 1 fsdf
2 1 fsdf
3 1 fsdf
4 1 fsdf
5 1 fsdf
6 1 fsdf
7 2 fsdf
8 2 fsdffd
9 3 fsdffd
10 3 fsdffd
11 3 fse

已选择11行。

SQL> select * from (select id, kind, title, row_number() over (partition by kind order by id) r from t) where r<=5;

ID KIND TITLE R
---------- ---------- ---------- ----------
1 1 fsdf 1
2 1 fsdf 2
3 1 fsdf 3
4 1 fsdf 4
5 1 fsdf 5
7 2 fsdf 1
8 2 fsdffd 2
9 3 fsdffd 1
10 3 fsdffd 2
11 3 fse 3

已选择10行。

补充空行的问题再找人续续。:-)

[小灰狼W]

SELECT ID,kind,title,CONTENT
from(
SELECT tmp.*,
  row_number()OVER(PARTITION BY flag ORDER BY ID NULLS LAST) rn
from(
SELECT t.*,kind flag FROM t
UNION ALL 
SELECT *
FROM (SELECT NULL A,NULL b,NULL c,NULL d FROM dual CONNECT BY ROWNUM<5),
(SELECT DISTINCT kind FROM t))tmp)
where rn<=5

如果数据量大，这样性能会很差
而且，这个需求...不应该放在sql里面实现

[zhangwonderful]

[Quote=引用 4 楼 wildwave 的回复:]

SQL code

SELECT ID,kind,title,CONTENT
from(
SELECT tmp.*,
row_number()OVER(PARTITION BY flag ORDER BY ID NULLS LAST) rn
from(
SELECT t.*,kind flag FROM t
UNION ALL
SELECT *
FROM (SELECT NULL A,N……
[/Quote]
支持

[牛叔]

[Quote=引用 4 楼 wildwave 的回复:]

SQL code

SELECT ID,kind,title,CONTENT
from(
SELECT tmp.*,
row_number()OVER(PARTITION BY flag ORDER BY ID NULLS LAST) rn
from(
SELECT t.*,kind flag FROM t
UNION ALL
SELECT *
FROM (SELECT NULL A,N……
[/Quote]


纯粹给你顶的
好久没接分了。。。

[碧水幽幽泉]

[Quote=引用 4 楼 wildwave 的回复:]
SQL code

SELECT ID,kind,title,CONTENT
from(
SELECT tmp.*,
row_number()OVER(PARTITION BY flag ORDER BY ID NULLS LAST) rn
from(
SELECT t.*,kind flag FROM t
UNION ALL
SELECT *
FROM (SELECT ……
[/Quote]
支持狂浪！

[yuxinglian]

在存储过程里写就容易多了

[pgameli]

今天早上终于想出来办法了
最终还是谢谢各位了
我用的是sql实现
大概思路如下：
select * from t whre kind in (select kind from t group by kind)
union all
select * from t ,(select count(id) cnt kind from t group by kind) a whre a.cnt<=4 and a.cnt>0 a.kind=kind
union all
select * from t ,(select count(id) cnt kind from t group by kind) a whre a.cnt<=3 and a.cnt>0 a.kind=kind
union all
select * from t ,(select count(id) cnt kind from t group by kind) a whre a.cnt<=2 and a.cnt>0 a.kind=kind
union all
select * from t ,(select count(id) cnt kind from t group by kind) a whre a.cnt<=1 and a.cnt>0 a.kind=kind