每个不同列值的自动编号和重置计数 [英] Auto number and reset count for each different column value

问题描述

我试图弄清楚如何为给定列中的每个不同值重置mysql中的行号.也许最好用一个例子来解释:

I'm attempting to figure out how I can reset the row numbering in mysql for each different value in a given column. Probably best explained with an example:

我有一组进行客户访问的用户，每个客户可能会被访问​​多次，并且我的表格记录了访问的日期(但不是第一次，第二次，第三次访问).所以我的桌子看起来像:

I have a set of users who make client visits, each client may be visited more than once and my table records the date the visit was made (but not whether this was the first,second,third etc visit). So my table looks like:

clientid   visitdate
100        10-apr-2012
101        15-apr-2012
101        25-apr-2012
102        26-apr-2012
100        28-apr-2012

我要查找的是在给定的日期范围内进行了两次(例如)第二次访问.因此，在上面的数据中，有3次首次访问和2次第二次访问.

What I'm looking to find out is how many (e.g.) second visits were made for a given date period. So in the data above, there are 3 first visits and 2 second visits.

我假设我需要使用类似@rownum功能的东西，

I'm assuming I need to use something like the @rownum functionality, like this:

SELECT visitdate，clientid @rownum:= @rownum + 1作为访问的行，(SELECT @rownum:= 0)a ORDER BY clientid，visitdate

SELECT visitdate, clientid @rownum := @rownum + 1 as row from visit, (SELECT @rownum := 0) a ORDER BY clientid,visitdate

这给了我

clientid   visitdate     row
100        10-apr-2012   1
100        28-apr-2012   2
101        15-apr-2012   3
101        25-apr-2012   4
102        26-apr-2012   5

但是我真正想要的是:

clientid   visitdate     row
100        10-apr-2012   1
100        28-apr-2012   2
101        15-apr-2012   1
101        25-apr-2012   2
102        26-apr-2012   1

我想弄清楚的是如何为每个clientid重置行计数器.

What I'm having trouble figuring out is how to reset the row counter for each clientid.

也许我用错误的方式来实现，我可以在应用程序(PHP)代码中进行这些计算，但是感觉这应该是在db中可以实现的.

Perhaps I'm approaching this in the wrong way, and I can do these calculations in the application (PHP) code, but it feels like something that should be achievable within the db.

我已经看到了SQLServer的方法(例如:

I've seen approaches for SQLServer (eg: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/74891/Auto-number-and-reset-based-on-data-value-in-a), but doesn't seem this works for mysql?

非常感谢您的帮助/建议， 干杯， 亚历克斯

Any help/suggestions much appreciated, Cheers, Alex

推荐答案

未经测试，但这可以解决问题:

Not tested, but this should do the trick:

SELECT
IF(@prev != a.clientid, @rownum:=1, @rownum:=@rownum+1) as rownumber, @prev:=a.clientid, a.*
FROM (
SELECT 
visitdate, 
clientid 
FROM visit, (SELECT @rownum := 0, @prev:='') sq
ORDER BY clientid,visitdate
) a

顺便说一句，没有"@rownum功能"，它只是一些自制的解决方案"，@ rownum只是一个变量.您也可以使用@whatever来命名.

And btw, there is no "@rownum functionality", it's just some selfmade "solution", @rownum is just a variable. You could as well name it @whatever.

这篇关于每个不同列值的自动编号和重置计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！