有这样一个表TABLE
就是要取出ABCDE这5列
A B C D 组合起来肯定是惟一的
但是 ABC相同情况下 只需要取出最大D的这一行数据就好(筛选条件应该跟E无关)
例如:对于 ABCED
(注意E是排在D前面的)----------------------------------------------------------
| A | B | C | E | D | | |
----------------------------------------------------------
| 11 | 22 | 33 | A | 0 | | |
----------------------------------------------------------
| 11 | 22 | 33 | B | 2 | | |
----------------------------------------------------------
| 11 | 22 | 33 | C | 1 | | |
----------------------------------------------------------
只需要取出
| 11 | 22 | 33 | B | 2 | | |就好再例如
对于下面这个----------------------------------------------------------
| A | B | C | E | D | | |----------------------------------------------------------
| 11 | 22 | 33 | A | 0 | | |
----------------------------------------------------------
| 11 | 22 | 33 | B | 2 | | |
----------------------------------------------------------
| 11 | 22 | 33 | C | 1 | | |
----------------------------------------------------------
| 22 | 22 | 33 | D | 0 | | |
----------------------------------------------------------
| 22 | 22 | 33 | B | 1 | | |
----------------------------------------------------------| 33 | 22 | 33 | C | 2 | | |
----------------------------------------------------------
| 33 | 22 | 33 | A | 3 | | |
----------------------------------------------------------
| 44 | 22 | 33 | K | 0 | | |
----------------------------------------------------------
| 44 | 22 | 33 | A | 1 | | |
----------------------------------------------------------
| 55 | 22 | 33 | Z | 1 | | |
----------------------------------------------------------
需要取出
| 11 | 22 | 33 | B | 2 | | |
----------------------------------------------------------
| 22 | 22 | 33 | B | 1 | | |
----------------------------------------------------------
| 33 | 22 | 33 | A | 3 | | |
----------------------------------------------------------
| 44 | 22 | 33 | A | 1 | | |
----------------------------------------------------------
| 55 | 22 | 33 | Z | 1 | | |
----------------------------------------------------------
现在有用这个max(rowid)方法
但是max(rowid)好像是跟列的排序有关的
如果在table定义的时候,D列排在E列前面的话
就能正确地取出结果
但是E列排在D列前面的话
max(rowid)是会根据E的大小来选择哪一行数据select * from Table where rowid in (select max(rowid) from Table group by A || B || C);请帮忙写下这个SQL语句
(不管table定义的时候列的顺序)
运行ok的话马上结贴给分!
多谢!!!sql
就是要取出ABCDE这5列
A B C D 组合起来肯定是惟一的
但是 ABC相同情况下 只需要取出最大D的这一行数据就好(筛选条件应该跟E无关)
例如:对于 ABCED
(注意E是排在D前面的)----------------------------------------------------------
| A | B | C | E | D | | |
----------------------------------------------------------
| 11 | 22 | 33 | A | 0 | | |
----------------------------------------------------------
| 11 | 22 | 33 | B | 2 | | |
----------------------------------------------------------
| 11 | 22 | 33 | C | 1 | | |
----------------------------------------------------------
只需要取出
| 11 | 22 | 33 | B | 2 | | |就好再例如
对于下面这个----------------------------------------------------------
| A | B | C | E | D | | |----------------------------------------------------------
| 11 | 22 | 33 | A | 0 | | |
----------------------------------------------------------
| 11 | 22 | 33 | B | 2 | | |
----------------------------------------------------------
| 11 | 22 | 33 | C | 1 | | |
----------------------------------------------------------
| 22 | 22 | 33 | D | 0 | | |
----------------------------------------------------------
| 22 | 22 | 33 | B | 1 | | |
----------------------------------------------------------| 33 | 22 | 33 | C | 2 | | |
----------------------------------------------------------
| 33 | 22 | 33 | A | 3 | | |
----------------------------------------------------------
| 44 | 22 | 33 | K | 0 | | |
----------------------------------------------------------
| 44 | 22 | 33 | A | 1 | | |
----------------------------------------------------------
| 55 | 22 | 33 | Z | 1 | | |
----------------------------------------------------------
需要取出
| 11 | 22 | 33 | B | 2 | | |
----------------------------------------------------------
| 22 | 22 | 33 | B | 1 | | |
----------------------------------------------------------
| 33 | 22 | 33 | A | 3 | | |
----------------------------------------------------------
| 44 | 22 | 33 | A | 1 | | |
----------------------------------------------------------
| 55 | 22 | 33 | Z | 1 | | |
----------------------------------------------------------
现在有用这个max(rowid)方法
但是max(rowid)好像是跟列的排序有关的
如果在table定义的时候,D列排在E列前面的话
就能正确地取出结果
但是E列排在D列前面的话
max(rowid)是会根据E的大小来选择哪一行数据select * from Table where rowid in (select max(rowid) from Table group by A || B || C);请帮忙写下这个SQL语句
(不管table定义的时候列的顺序)
运行ok的话马上结贴给分!
多谢!!!sql
解决方案 »
- Oracle10 的导出问题
- 一个100万条记录表,目前查询速度过慢,让你给出5条优化措施
- oracle的数据备份的大小是不是和它的tablespace的大小有关?
- orcale中如何判断 用户,关系和表是否存在?
- ORA-02068、ORA-03113错误,在线等!
- 那里有关于连接oracle和web services的资料
- 如何得到数据库中使用A表主键作为外键的表?
- 主键生成策略的优略比较
- 请问ORACLE中取整是哪个函数,不要四舍五入,多谢
- 哪位大侠能提供Oracle 10.2.0.3或10.2.0.4的下载地址,帮小弟解燃眉之急,跪谢!
- 日期1980与2080
- 这个sql语句该怎么写?!!!
from test2 w, (select A, B, C, max(D) as F from test2 group by A, B, C) t
where w.A = t.A
and w.B = t.B
and w.C = t.C
and D = t.F
order by w.A, w.B, w.C
a VARCHAR2(20),
b VARCHAR2(20),
c VARCHAR2(20),
d VARCHAR2(20),
e VARCHAR2(20)
);
INSERT INTO foo VALUES ('11','22','33','A','0');
INSERT INTO foo VALUES ('11','22','33','B','2');
INSERT INTO foo VALUES ('11','22','33','C','1');
INSERT INTO FOO VALUES ('11','22','33','A','0');
INSERT INTO FOO VALUES ('11','22','33','B','2');
INSERT INTO FOO VALUES ('11','22','33','C','1');
INSERT INTO FOO VALUES ('22','22','33','D','0');
INSERT INTO FOO VALUES ('22','22','33','B','1');
INSERT INTO FOO VALUES ('33','22','33','C','2');
INSERT INTO FOO VALUES ('33','22','33','A','3');
INSERT INTO FOO VALUES ('44','22','33','K','0');
INSERT INTO FOO VALUES ('44','22','33','A','1');
INSERT INTO FOO VALUES ('55','22','33','Z','1');
查询SQL:SELECT O.A,O.B,O.C,O.D,O.E FROM
(SELECT a,b,c,d,e,MAX(e) over(PARTITION BY a,b,c) AS eg FROM foo) o
WHERE o.eg =e;
查询结果:A B C D E
11 22 33 B 2
11 22 33 B 2
22 22 33 B 1
33 22 33 A 3
44 22 33 A 1
55 22 33 Z 1
from foo w, (select A, B, C, max(E) as F from foo group by A, B, C) t
where w.A = t.A
and w.B = t.B
and w.C = t.C
and E = t.F
order by w.A, w.B, w.C
查询结果:11 22 33 2 B
22 22 33 1 B
33 22 33 3 A
44 22 33 1 A
55 22 33 1 Z
3L的换下列的顺序也ok
谢谢咯
给分结贴!