select k1,max(k2),.... from a group by k1 having mod(max(k2),2)=0;
select a.* from (select k1,max(k2) k22 from a where mod(k2,2) = 0 group by k1) b,a where a.k1=b.k1 and a.k2 = b.k22;
group by 中没有的列怎么选?
group by中没有的列必须用分组函数处理,如max、min、avg等。
所以select k1,max(k2),.... from a group by k1 having mod(max(k2),2)=0; 不可以,其他列没法选。 好像qqqdong的最好
不用group by 怎么选出k1相同中k2最大的行? group by 语句是肯定要用的。
你们看看qqqdong的,好像非常棒
具体那个效率高,在SQLPLUS中测试一下不就知道了。
select a.* from (select k1,max(k2) k22 from a where mod(k2,2) = 0 group by k1) b,a where a.k1=b.k1 and a.k2 = b.k22 和 select * from table a where a.k2 = (select max(b.k2) from table b where a.k1 = b.k1 and mod(b.k2,2) = 0) 都行 但第二种方法快!
我感觉你的问题有点模糊... 而且如果K2是奇数则不要->这句话,是说最大的一个数字,是奇数,你不要,要比它小的偶数哪?还是这个满足条件的k1这组,整个都不要哪?如果,是前者的话,我有个语句,不知道,怎么样! select * from table a,(select k1,max(k2) from table group by k1 where mod(k2,2) = 0) b where a.k1=b.k1 and a.k2=b.k2
select k1,max(k2) from ( select * from hdb x where exists (select 'x' from hdb y where y.rowid != x.rowid and y.k1 = x.k1 and mod(y.k2,2)=0) ) group by k1;我的表里面有100万条记录,出来的结果有1万多条。 结果如下 已选择18099行。已用时间: 00: 00: 51.54
select k1,max(k2) from ( select * from hdb x where exists (select 'x' from hdb y where y.rowid != x.rowid and y.k1 = x.k1 and mod(y.k2,2)=0) ) group by k1; 这个不在group by中也不用函数的列没法选,没有用的select * from table a,(select k1,max(k2) from table group by k1 where mod(k2,2) = 0) b where a.k1=b.k1 and a.k2=b.k2 这个太普通了。看看qqqdong的好吗?
qqqdong的好象和这个比较像 select * from table a where a.k2=(select max(k2) from table b group by k1 where mod(k2,2) = 0) 那么是select max(k2) from table b group by k1 where mod(k2,2) = 0快呢?还是select max(b.k2) from table b where a.k1 = b.k1 and mod(b.k2,2) = 0快? 我感觉使用group应该快一些,后者好像自己在实现group一样,但是也不一定,group可能考虑的比较多,而这里自己实现的毕竟只考虑比大小。 自己试一下吧! 一家之言。
试一试这个吧,我的数据表有100多万笔都是这样取数据的﹐不过在子查询中要加条件的﹐一次取出10万笔是平常的select * from (select K1,K2, row_number() over(PARTITION BY K1 ORDER BY K2 DESC) rowno from A) where rowno=1 and mod(K2,2)=0
SELECT * FROM A WHERE (K1,K2) IN( SELECT K1,MAX(K2) FROM A WHERE MOD(K2,2)!=0 GROUP BY K1 )
select * from a where (k1,k2) in (select k1, max(k2) from a group by k1 having mod(k2,2)!=0);
select * from a where (k1,k2) in (select k1, max(k2) from a group by k1 having mod(k2,2)!=0);
select * from a where (k1,k2) in (select k1, max(k2) from a group by k1 having mod(k2,2)!=0);
where a.k1=b.k1 and a.k2 = b.k22;
好像qqqdong的最好
group by 语句是肯定要用的。
where a.k1=b.k1 and a.k2 = b.k22
和
select * from table a where a.k2 = (select max(b.k2) from table b
where a.k1 = b.k1 and mod(b.k2,2) = 0)
都行
但第二种方法快!
而且如果K2是奇数则不要->这句话,是说最大的一个数字,是奇数,你不要,要比它小的偶数哪?还是这个满足条件的k1这组,整个都不要哪?如果,是前者的话,我有个语句,不知道,怎么样!
select * from table a,(select k1,max(k2) from table group by k1 where mod(k2,2) = 0) b where a.k1=b.k1 and a.k2=b.k2
( select * from hdb x
where exists (select 'x' from hdb y
where y.rowid != x.rowid and y.k1 = x.k1 and mod(y.k2,2)=0)
)
group by k1;我的表里面有100万条记录,出来的结果有1万多条。
结果如下
已选择18099行。已用时间: 00: 00: 51.54
( select * from hdb x
where exists (select 'x' from hdb y
where y.rowid != x.rowid and y.k1 = x.k1 and mod(y.k2,2)=0)
)
group by k1;
这个不在group by中也不用函数的列没法选,没有用的select * from table a,(select k1,max(k2) from table group by k1 where mod(k2,2) = 0) b where a.k1=b.k1 and a.k2=b.k2
这个太普通了。看看qqqdong的好吗?
select * from table a where a.k2=(select max(k2) from table b group by k1 where mod(k2,2) = 0)
那么是select max(k2) from table b group by k1 where mod(k2,2) = 0快呢?还是select max(b.k2) from table b where a.k1 = b.k1 and mod(b.k2,2) = 0快?
我感觉使用group应该快一些,后者好像自己在实现group一样,但是也不一定,group可能考虑的比较多,而这里自己实现的毕竟只考虑比大小。
自己试一下吧!
一家之言。
(select K1,K2,
row_number() over(PARTITION BY K1 ORDER BY K2 DESC) rowno
from A)
where rowno=1 and mod(K2,2)=0
WHERE (K1,K2) IN(
SELECT K1,MAX(K2)
FROM A
WHERE MOD(K2,2)!=0
GROUP BY K1
)
where (k1,k2) in (select k1, max(k2)
from a
group by k1
having mod(k2,2)!=0);
where (k1,k2) in (select k1, max(k2)
from a
group by k1
having mod(k2,2)!=0);
where (k1,k2) in (select k1, max(k2)
from a
group by k1
having mod(k2,2)!=0);