比较mysql中同一表中的行 [英] Compare rows in same table in 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 

我需要将第1行的价格与第2行的价格进行比较，并得出给定的样本，第2行的价格小于第1行的价格，这意味着交易者xyz将价格提高了一次，并且当我们比较第2行的价格时也是如此如果第3行的价格高于第2行的价格，这意味着交易员abc也将价格提高了一次.因此，以这种方式，我需要比较整个表格，并找出每个交易者提高或降低价格的次数...

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

推荐答案

您可以执行自我联接"(将表自身联接)以执行查询.这里最棘手的部分是知道将行插入表中的顺序，并且仅比较顺序(临时)相邻的行.我假设您有某种TIMESTAMP列，它将告诉您哪些价格变化是在先前价格变化之后发生的.如果不是，那么"ID"可能会通知您(在较小的ID后面插入较大的ID行).

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).

调用表'TAB'，使用'TRADER'提供联接，并使用'ID'提供Order，查询将需要三向自联接，如下所示:

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:

• 选项卡a:用于比较的最新行
• 标签b:要与其进行比较的前一行
• 标签c:&之间的一行b时间方向(不应存在)

因为我们实际上不希望该行存在，所以我们对选项卡c"执行了LEFT OUTER JOIN.在where子句中，我们仅将结果过滤到不存在"tab c"行的结果.

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.

最后，查询在交易者上执行"GROUP BY"，并且SUM()通过比较"a"行和"b"行中的价格来计算增量和减量.

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!

约翰...

这篇关于比较mysql中同一表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！