如果你的举例是正确的,意思就是说只要看每条记录的下一条的,那么可以试试看这个: select id,salary from ( select id, salary, lag(salary) over (order by id) as prevsalary from mytable ) where salary <> prevsalary or prevsalary is null order by id
salary和id 是 not null的吧 我给的例子是以这个为前提的哦 还有一个前提是 table里面没什么别的字段需要影响分组
select * from ( select historyid, factoryid, materialid, price, lag(price) over (order by id) as prevprice, changedate from mytable ) where price <> prevprice or prevprice is null order by historyid那不差不多的么 多加几个字段就行了
select * from ( select historyid, factoryid, materialid, price, lag(price) over (order by historyid) as prevprice, changedate from mytable ) where price <> prevprice or prevprice is null order by historyid重新写了一下但是我估计你可能需要根据FACTORY分组
楼主你该给我两次分 HOHO
这样是可以的,但我在where里加了条件后就不行了 where (price <> prevprice or prevprice is null) and materialid='XXX'
呵呵不好意思小虫,我再表达次 1。查询时必须指定materialid 和 factoryid,所以,可以不用根据这两个条件分组 2。由于指定materialid和factoryid,所以我必须把它加到查询条件中,也就是 where (price <> prevprice or prevprice is null) and materialid='XXX' and factoryid='YYY' 3。不加查询条件,直接用你给出的sql进行查询,得到了我想要的结果,但加上查询条件后,结果如下1, 100 2, 100 3, 200 4, 100也就是说,并未把2,100挑出去
把查询条件加到子查询中,可以实现了 select * from ( select historyid, factoryid, materialid, price, lag(price) over (order by historyid) as prevprice, changedate from mytable where materialid='XXX' and factoryid='YYY' ) where price <> prevprice or prevprice is null order by historyid
where (price <> prevprice or prevprice is null) and materialid='XXX' and factoryid='YYY' 这样应该没问题的啊你在price <> prevprice or prevprice is null外面加上了括号了呀
我上面那种写法,也可以了,把条件加到原来的子查询中 select * from ( select historyid, factoryid, materialid, price, lag(price) over (order by historyid) as prevprice, changedate from mytable where materialid='XXX' and factoryid='YYY' ) where price <> prevprice or prevprice is null order by historyid
意思就是说,如果相邻数据中的salary一样才取id最小的,比如id1和id2的salary相同,就只取id1的数据。如果不是相邻数据,比如id4和id2的salary相同,但不相邻,所以还是取出来。
再举个例子
id,salary
1, 100
2, 100
3, 200
4, 100
5, 300
6, 200
7, 200
8, 900要求查询结果:
id, salary
1, 100
3, 200
4, 100
5, 300
6, 200
8, 900
不知道说明白没。换句话说,就是相邻的记录才group by
select
id,salary
from
(
select
id,
salary,
lag(salary) over (order by id) as prevsalary
from mytable
)
where salary <> prevsalary or prevsalary is null
order by id
还有一个前提是 table里面没什么别的字段需要影响分组
等等我把实际结构贴上来
1, 0001, aaaa, 100, 2006/11/1
2, 0001, aaaa, 100, 2006/11/1
3, 0001, aaaa, 200, 2006/11/1
4, 0001, aaaa, 100, 2006/11/1就是想查询下物料历史价格,呵呵
*
from
(
select
historyid,
factoryid,
materialid,
price,
lag(price) over (order by id) as prevprice,
changedate
from mytable
)
where price <> prevprice or prevprice is null
order by historyid那不差不多的么 多加几个字段就行了
*
from
(
select
historyid,
factoryid,
materialid,
price,
lag(price) over (order by historyid) as prevprice,
changedate
from mytable
)
where price <> prevprice or prevprice is null
order by historyid重新写了一下但是我估计你可能需要根据FACTORY分组
where (price <> prevprice or prevprice is null) and materialid='XXX'
楼主我不好意思要说你表达得不清楚啊
1。查询时必须指定materialid 和 factoryid,所以,可以不用根据这两个条件分组
2。由于指定materialid和factoryid,所以我必须把它加到查询条件中,也就是
where (price <> prevprice or prevprice is null) and materialid='XXX' and factoryid='YYY'
3。不加查询条件,直接用你给出的sql进行查询,得到了我想要的结果,但加上查询条件后,结果如下1, 100
2, 100
3, 200
4, 100也就是说,并未把2,100挑出去
select
*
from
(
select
historyid,
factoryid,
materialid,
price,
lag(price) over (order by historyid) as prevprice,
changedate
from mytable
where materialid='XXX' and factoryid='YYY'
)
where price <> prevprice or prevprice is null
order by historyid
这样应该没问题的啊你在price <> prevprice or prevprice is null外面加上了括号了呀
select
*
from
(
select
historyid,
factoryid,
materialid,
price,
lag(price) over (order by historyid) as prevprice,
changedate
from mytable
where materialid='XXX' and factoryid='YYY'
)
where price <> prevprice or prevprice is null
order by historyid
--
我的意思是你加上了括号是正确的
要不你把我的例子做成subquery试试看不就行了or和and的优先级我还是知道的
你可以把我给你的sql做成view你就可以很简单的查询了
反之利用你的方法你就没有办法做成view
=======================
三次都该给的,不过怕有倒分嫌疑,哈哈
关键你能看懂analytic function的用法 以后你就能自己解决类似问题了