下面为您介绍的SQL语句用于实现DB2分类取前N个记录,如果您遇到过DB2分类取前N个记录的问题,不妨一看。
有一个表t1,结构如下:
类别编号 说明 排序
a aa 1
a aa2 4
a aa3 6
b bb 1
b bb2 3
b bb3 5
c cc 1
c cc2 5
c cc3 7
需要查询出来的结果是每个类别的头2条记录,按排序进行排序,结果如下:
类别编号 说明 排序
a aa 1
a aa2 4
b bb 1
b bb2 3
c cc 1
c cc2 5
create table t1(
yTypeCode varchar(10),
yMemo varchar(10),
yOrder int
)
insert into t1(yTypeCode,yMemo,yOrder) values( 'a ', 'aa ',1);
insert into t1(yTypeCode,yMemo,yOrder) values( 'a ', 'aa2 ',4);
insert into t1(yTypeCode,yMemo,yOrder) values( 'a ', 'aa3 ',6);
insert into t1(yTypeCode,yMemo,yOrder) values( 'b ', 'bb ',1);
insert into t1(yTypeCode,yMemo,yOrder) values( 'b ', 'bb2 ',2);
insert into t1(yTypeCode,yMemo,yOrder) values( 'b ', 'bb3 ',3);
insert into t1(yTypeCode,yMemo,yOrder) values( 'c ', 'cc ',1);
insert into t1(yTypeCode,yMemo,yOrder) values( 'c ', 'cc2 ',5);
insert into t1(yTypeCode,yMemo,yOrder) values( 'c ', 'cc3 ',8)
实现;
SELECT * FROM t1 t
WHERE (SELECT count(*) FROM t1 WHERE yTypeCode=t.yTypeCode AND yOrder<t.yOrder)<2