sql筛选查询

 考察你sql功底
 select * from oracle where (num) in (select max(num) from oracle where id = 1) or (id,num) in (select max(num),id from oracle group by id)

 好像 有地方反了
select * from oracle where (num) in (select max(num) from oracle where id = 1) or (num,id) in (select max(num),id from oracle group by id)

 还少个where ...
select * from oracle where (num) in (select max(num) from oracle where id = 1) or (num,id) in (select max(num),id from oracle group by id where id!=1)

感谢你的回答，但按照你的思路，id为1的查出了两条，没有达到去重的效果

那前面一个子查询 仿照后面一个子查询做 

select * from oracle where (num,id) in (select max(num),id from oracle group by id where id = 1) or (num,id) in (select max(num),id from oracle group by id where id!=1)

select id,type ,num from (select id,type,num from 

(select id ,type ,num from (select id ,type ,num from oracle ORDER BY type asc , num desc) o1 GROUP BY id

union

select id ,type ,num from (select id ,type ,num from oracle ORDER BY num desc ) o2 GROUP BY id) o

order by type ) o GROUP BY id

mysql不能先排序再分组有点蛋疼

感谢你提供的思路，但这个sql有点问题：

“(select id ,type ,num from (select id ,type ,num from oracle ORDER BY type asc , num desc) o1 GROUP BY id”

select只能查询group by子句中的字段

假如type类型有多个（1,2,3），查找的结果为type=2的，排序再分组就不行了吧

这张表在oracle数据库里，可以用oracle语句

一楼那位大哥的思路还是不行。。两个字查询会存在id重复的数据

select * from test where (id,type,num) in 
(select id,type,max(num) from test where type = 1 group by id,type) 
or (num,id,type) in (select max(num),id,type from test where type!=1 
and id not in (select distinct a.id from test a,test b where a.id = b.id and a.type != b.type)
group by id,type);
这样应该对了....

没有type 为1 的数据 并不是 type!=1 23333.

样例数据可能太少了...
type!=1 and id not in (select distinct a.id from test a,test b where a.id = b.id and a.type != b.type)
这句话可能还是不能阐述 没有type为1时

id not in (select distinct a.id from test a,test b where a.id = b.id and a.type != b.type and a.type = 1)

is not in (select id from oracle a where type = 1 GROUP BY id) 这样应该能排除type=1的情况

目前我使用的方法是：

select t1.id,( CASE t2.num when null then t1.num else t2.max(num) end) num from (

(select id,max(num) num from oracle where type = 1 GROUP BY id ) t1

left join 

(select id,max(num) num from oracle GROUP BY id) t2 on t1.id = t2.id

)

因为是oracle数据库，有没有更高效的方法

结果只用显示id，和num就行，type可以不显示

 我方法你不想要吗2333....

emmmm 整理了一下你的方法

select * from oracle where (id,num) in 

(select id,max(num) from oracle where type = 1 group by id)

or (num,id) in (select max(num),id from oracle where type!=1 

and id not in 

(select id from oracle a where type = 1 GROUP BY id )

group by id);

结果没问题，但要查4次表

没有更高效的就采纳你的方法了

select a.id,a.type, max(a.num) from da_emp1 a where a.type='1' group by a.id,a.type

union all select b.id,b.type,b.num from da_emp1 b where b.type='2' and b.num='2'

select c.id,min(c.type) as type,max(c.num) as num from test as c group by c.id

2333333点了45次采纳..

点完没有任何提示，还以为出bug了

啥呀，我那只是把结果集拼出来，但是能出来你想要的结果，但是错的，表名da_emp1改成orcale 你的表名就行

type类型不只是1和2，我样例数据只写了一点

嗯，所以一楼大哥很强