下面為您介紹的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