productID dj je sl date ID
A 10 20 2 2012-01-01 1
A 10 50 5 2012-01-02 2
A 9 27 3 2012-01-03 4
B 5 10 2 2012-01-01 1
B 5 20 4 2012-01-02 3
B 6 12 2 2012-01-04 5
表结构如上所示,我现在要查找一段时间内所有产品价格变动的日期,求语句,求思路,谢谢!
A 10 20 2 2012-01-01 1
A 10 50 5 2012-01-02 2
A 9 27 3 2012-01-03 4
B 5 10 2 2012-01-01 1
B 5 20 4 2012-01-02 3
B 6 12 2 2012-01-04 5
表结构如上所示,我现在要查找一段时间内所有产品价格变动的日期,求语句,求思路,谢谢!
--这样如何,
WITH Test(productID,dj,je,sl,date,ID)
AS
(
select 'A','10','20','2','2012-01-01','1' union all
select 'A','10','50','5','2012-01-02','2' union all
select 'A','9','27','3','2012-01-03','4' union all
select 'B','5','10','2','2012-01-01','1' union all
select 'B','5','20','4','2012-01-02','3' union all
select 'B','6','12','2','2012-01-04','5'
)
SELECT * FROM Test AS t1 WHERE NOT EXISTS(SELECT 1 FROM Test AS t2 WHERE t1.productID = t2.productID AND t1.dj = t2.dj AND t1.date < t2.date)productID dj je sl date ID
--------- ---- ---- ---- ---------- ----
A 10 50 5 2012-01-02 2
A 9 27 3 2012-01-03 4
B 5 20 4 2012-01-02 3
B 6 12 2 2012-01-04 5(4 行受影响)
WITH Test(productID,dj,je,sl,date,ID)
AS
(
select 'A','10','20','2','2012-01-01','1' union all
select 'A','10','50','5','2012-01-02','2' union all
select 'A','9','27','3','2012-01-03','4' union all
select 'B','5','10','2','2012-01-01','1' union all
select 'B','5','20','4','2012-01-02','3' union all
select 'B','6','12','2','2012-01-04','5'
)
select * from
(select *,ROW_NUMBER() over (partition by productID,dj order by date desc ) as cid
from test) a
where cid=1