有如下表结构及数据:
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来学习用,哪个版本的适合学习一下,且不耗费电脑资源呢,版本高低无所谓
- 数据库每天凌晨0点自动备份,到早上8点数据库夸了,那0点到8点的数据就恢复不了了吧?
- 高手,求一oracle sql查询功能
- 求一条超难的sql语句
- ORACLE 一个字段能存多少字符串?
- 怎样复制不同数据库的资料??
- 能否通过存储过程把文本文件中的数据导入数据库中?
- 我的Oracle安装完成后 服务启动以后怎么数据库不起动?
- 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');