建表:
create table t_test(
pk_id VARCHAR2(50 BYTE) not null,g_name VARCHAR2(200 BYTE),
f_description VARCHAR2(400 BYTE),
f_order NUMBER,
constraint pk_t_test primary key (pk_id)
)
插数据:
insert into t_test values('a1','张三','',1);
insert into t_test values('a2','张三','',2);
insert into t_test values('a3','李四','',3);
insert into t_test values('a4','李四','',4);
insert into t_test values('a5','李四','',5);
原博友的写法是:
SELECT *
FROM (SELECT PK_ID,
G_NAME,
F_DESCRIPTION,
F_ORDER,
ROW_NUMBER() OVER(PARTITION BY G_NAME ORDER BY F_ORDER) RANK
FROM T_TEST) T
WHERE T.RANK = 1;
其实这样写更简洁些~~
SELECT * FROM T_TEST WHERE ROWID IN (
SELECT MIN(ROWID) FROM T_TEST t GROUP BY t.g_name
)