mysql 行比较,比较MySQL中同一表中的行

I have a mysql table like below

id trader price

111 abc 5

222 xyz 5.20

333 abc 5.70

444 xyz 5

555 abc 5.20

I need to compare row 1's price with row 2's price and accroding to the given sample row 2's price is less than row 1's price which means that trader xyz increased the price once, and in the same way when we compare row 2's price with row 3's price where row 3's price is higher than row 2's price which means trader abc also increased the price once. So in this manner I need to compare the entire table and find how many times each trader increased or decreased price...

I dont have any idea to do this, can someone please help me on this

解决方案

You can perform a "self-join" (joining the table to itself) to perform queries. The tricky part here is knowing the order in which rows were inserted into the table, and only comparing rows that are sequentially (temporally) adjacent. I'm assuming you have some sort of TIMESTAMP column that will tell you which price changes came after the prior ones. If not, then perhaps "ID" can inform you of that (the greater ID row being inserted after the lesser ID).

Calling your table 'TAB', using 'TRADER' to provide the join, and using 'ID' to provide the Order, the query would require a three-way self-join such as follows:

SELECT a.trader

, SUM(IF(a.price > b.price, 1, 0)) nbr_incr

, SUM(IF(a.price < b.price, 1, 0)) nbr_decr

, SUM(IF(a.price = b.price, 1, 0)) nbr_same

FROM tab a

JOIN tab b

ON a.trader = b.trader AND a.id > b.id

LEFT OUTER JOIN tab c

ON a.trader = c.trader AND a.id > c.id AND b.id < c.id

WHERE c.id IS NULL

GROUP BY a.trader

The above query joins the table to itself twice so that each tab represents the following:

tab a : The more recent row for comparison

tab b : The immediately prior row to compare against

tab c : A row between a & b timewise (should not exist)

We perform a LEFT OUTER JOIN to 'tab c' because we do not actually want that row to exist. In the where clause, we filter our results only to the results where a 'tab c' row does not exist.

Finally, the query performs a 'GROUP BY' on the trader, and SUM()s the Increments and Decrements by comparing the price from the 'a' and 'b' rows.

This was a fun challenge. Hope this helps!

john...