有如下表结构及数据:
id claimno status
1 Z001 m
2 Z001 1
3 Z001 t
4 Z002 t
5 Z002 1
6 Z003 1
7 Z003 1
8 Z003 m
想得到如下数据:
id claimno status3 Z001 t
5 Z002 1
8 Z003 m
也就是说每个claimno中id最大的那一行
id claimno status
1 Z001 m
2 Z001 1
3 Z001 t
4 Z002 t
5 Z002 1
6 Z003 1
7 Z003 1
8 Z003 m
想得到如下数据:
id claimno status3 Z001 t
5 Z002 1
8 Z003 m
也就是说每个claimno中id最大的那一行
解决方案 »
- 如何设置oracle数据库不区分大小写
- 急急急..一个简单的sql语句不知道在Orcle中是否能执行..我这边没的Orcle环境。
- 一条检查是否为空的SQL语句
- 触发器的效率问题
- 我在执行SQL语句的时候出现了问题,请大家帮忙解决,谢谢!
- 请问存储过程中返回记录集包含BLOB 类型怎么处理?
- 建临时表的问题,请赐教!
- 新手:刚建的数据库,登录提示出错。(在线等待)
- 违反唯一约束条件(TSSH PK_SDSK_CPCODE_THS_NO)
- 根据部门分组,求出每个部门里面工资高于2000的百分比
- A SQL query question for oracle。
- 一条复杂的sql查询语句,不知道怎么写好点?????
From tab t1
where not exists (select 1
from tab t2
where t2.claimno = t1.claimno
and t2.id > t1.id);
WITH A AS
(SELECT 1 ID, 'Z001' CLAIMNO, 'M' T
FROM DUAL
UNION ALL
SELECT 2 ID, 'Z001' CLAIMNO, '1' T
FROM DUAL
UNION ALL
SELECT 3 ID, 'Z001' CLAIMNO, 'T' T
FROM DUAL
UNION ALL
SELECT 4 ID, 'Z002' CLAIMNO, 'T' T
FROM DUAL
UNION ALL
SELECT 5 ID, 'Z002' CLAIMNO, '1' T
FROM DUAL
UNION ALL
SELECT 6 ID, 'Z003' CLAIMNO, '1' T
FROM DUAL
UNION ALL
SELECT 7 ID, 'Z003' CLAIMNO, '1' T
FROM DUAL
UNION ALL
SELECT 8 ID, 'Z003' CLAIMNO, '8' T FROM DUAL)
SELECT ID, CLAIMNO, T
FROM A
WHERE ID IN (
SELECT MAX(ID)
FROM A
GROUP BY CLAIMNO) ORDER BY ID;
WITH A AS
(SELECT 1 ID, 'Z001' CLAIMNO, 'M' T
FROM DUAL
UNION ALL
SELECT 2 ID, 'Z001' CLAIMNO, '1' T
FROM DUAL
UNION ALL
SELECT 3 ID, 'Z001' CLAIMNO, 'T' T
FROM DUAL
UNION ALL
SELECT 4 ID, 'Z002' CLAIMNO, 'T' T
FROM DUAL
UNION ALL
SELECT 5 ID, 'Z002' CLAIMNO, '1' T
FROM DUAL
UNION ALL
SELECT 6 ID, 'Z003' CLAIMNO, '1' T
FROM DUAL
UNION ALL
SELECT 7 ID, 'Z003' CLAIMNO, '1' T
FROM DUAL
UNION ALL
SELECT 8 ID, 'Z003' CLAIMNO, '8' T FROM DUAL)
SELECT ID, CLAIMNO, T
FROM A A1
WHERE NOT EXISTS (SELECT 1
FROM A A2
WHERE A2.CLAIMNO = A1.CLAIMNO
AND A2.ID > A1.ID);
FROM (SELECT RANK() OVER(PARTITION BY CLAIMNO ORDER BY ID DESC) RK,
ID,
CLAIMNO,
STATUS
FROM TBL)
WHERE RK = 1
(select 1 id, 'Z001' claimno, 'm' status from dual union all
select 2, 'Z001', '1'from dual union all
select 3, 'Z001', 't' from dual union all
select 4, 'Z002', 't' from dual union all
select 5, 'Z002', '1' from dual union all
select 6, 'Z003', '1' from dual union all
select 7, 'Z003', '1' from dual union all
select 8, 'Z003', 'm' from dual
)
select * from (
select id,claimno,status,row_number() over (partition by claimno order by id desc) rn from t ) where rn=1
如果ID唯一的话也可以用
select t1.id,t1.claimno,t1.status from t t1,
(select max(id) id,claimno from t group by claimno) t2 where t1.id = t2.id
(
ID NUMBER(4),
ClaimNo VARCHAR2(20),
Status VARCHAR2(4)
);
INSERT INTO T173 VALUES(1, 'Z001', 'm');
INSERT INTO T173 VALUES(2, 'Z001', 'l');
INSERT INTO T173 VALUES(3, 'Z001', 't');
INSERT INTO T173 VALUES(4, 'Z002', 't');
INSERT INTO T173 VALUES(5, 'Z002', 'l');
INSERT INTO T173 VALUES(6, 'Z003', 'l');
INSERT INTO T173 VALUES(7, 'Z003', 'l');
INSERT INTO T173 VALUES(8, 'Z003', 'm');