Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

sql - Return rows only if they are different that previous days value

sales
-id
-user_id
-amount
-datetime

the sales table gets a new record for each user_id daily.

i want a select query that returns me all rows for the current day, only if the 'amount' is different that the previous days.

Update

5   123    700  2017/01/05
4   123    500  2017/01/04
3   123   1500  2017/01/03
2   123   1500  2017/01/02
1   123    500  2017/01/01

So if you search for records on the 5th, you will get 1 row since it is different that the previous days.

5   123    700  2017/01/05

But if you were run the query on the 3rd, since the amound $1500 is the same as on the 2nd, you will get 0 results back.

Hope this clears it up.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

You could try using the Lead() and Lag() SQL Analytic function for this

SELECT *
from (SELECT *, LAG(AMOUNT) over (partition by user_id order by datetime) as Yesterday_AMOUNT
      from SALES s
     ) x
where cast(s.datetime as date) = cast(getdaate() as date) and 
       x.AMOUNT <> s.Yesterday_AMOUNT;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share
...