如何在MySQL中查询列中的更改数量 [英] How to query number of changes in a column in MySQL

问题描述

我有一个表，该表存储具有两个属性的项目.因此该表包含三列:

I have a table that stores items with two properties. So the table has three columns:

 item_id  |  property_1  |  property_2  |  insert_time
  1       |   10         |  100         |  2012-08-24 00:00:01
  1       |   11         |  100         |  2012-08-24 00:00:02
  1       |   11         |  101         |  2012-08-24 00:00:03
  2       |   20         |  200         |  2012-08-24 00:00:04
  2       |   20         |  201         |  2012-08-24 00:00:05
  2       |   20         |  200         |  2012-08-24 00:00:06

也就是说，每当任何一项的 属性更改时，都会插入一个新行.还有一个存储插入时间的列.现在，我想获取property_2中的更改数量.对于上表，我应该得到

That is, each time either property of any item changes, a new row is inserted. There is also a column storing the insertion time. Now I want to get the number of changes in property_2. For the table above, I should get

item_id  |  changes_in_property_2
 1       |    2
 2       |    3

我怎么能得到这个?

推荐答案

我将使用用户定义的变量来跟踪上一行的值.

I would do it this way, with user-defined variables to keep track of the previous row's value.

SELECT item_id, MAX(c) AS changes_in_property_2
FROM (
    SELECT IF(@i = item_id, IF(@p = property_2, @c, @c:=@c+1), @c:=1) AS c, 
        (@i:=item_id) AS item_id,
        (@p:=property_2) 
    FROM `no_one_names_their_table_in_sql_questions` AS t,
    (SELECT @i:=0, @p:=0) AS _init
    ORDER BY insert_time
) AS sub
GROUP BY item_id;

这篇关于如何在MySQL中查询列中的更改数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！