需求:查询当天的a,b字段数据第一次出现,在之前出现过的需要去掉
‘in’:
sql = “select a,x,x,count(0) from ( select row_number() over (partition by b,a order by serial_number,time asc) IDS,b,a,time,x,x from 表 WHERE 条件and ( a,b) not in ( select a,b from ( select row_number() over (partition by b,a order by serial_number,time asc) IDS,b,a ,time,x,x FROM 表 where IDS =1 group by time,x,x”
in 的时候数据量大查询会非常慢,超过1000也会报错
'exists’:
select a,b,x,x,count(0) from (select row_number() over (partition by a , b order by serial_number,time asc) IDS, c.a,c.b,x,x from 表c WHERE 条件 and not exists ( select d.a from 表d WHERE 条件 and c.a = d.a and c.b = d.b ) ) where IDS=1 group by a,b,x,x
改为exists查询后,查询速度提高很多
参考:
[1]: https://blog.csdn.net/niugang0920/article/details/80023784
[2]: http://blog.itpub.net/31015730/viewspace-2147932
[3]: https://www.iteye.com/blog/yangzhonglei-699673