求一条SQL语句.

Question

id date
1  '5-1'
1  '7-20'
1  '6-2'
2  '5-4'
2  '3-2'

如何得到根据先后时间的次数:

id date   cunt
1  '5-1'   1   
1  '6-2'   2
1  '7-20'  3
2  '3-2'   1
2  '5-4'   2

有办法实现吗?希望大家能帮我写的通用一点,因为还会根据ID去关联user这张表.

Answer 1

基本思想如下：
对于每一行，找到表中日期小于等于该行的日期且ID与该行相同的行的数量，将该数量更新该CUNT字段。
由于不知道你的表名，我先创建了一张表并且插入了一些数据，语句如下：

CREATE TABLE TEST("ID" INTEGER,"DATE" DATE,"CUNT" INTEGER);
INSERT INTO TEST("ID","DATE") VALUES (1,'2013-01-02');
INSERT INTO TEST("ID","DATE") VALUES (1,'2013-02-02');
INSERT INTO TEST("ID","DATE") VALUES (1,'2013-03-02');
INSERT INTO TEST("ID","DATE") VALUES (2,'2013-01-03');
INSERT INTO TEST("ID","DATE") VALUES (2,'2013-02-04');

然后更新cunt：

UPDATE TEST AS T1 SET "CUNT"=(
SELECT COUNT(*) FROM TEST AS T2
WHERE T2."DATE"<=T1."DATE" AND T1."ID"=T2."ID"
);

结果如下：

希望帮到你

Answer 2

SET @num=0;
SET @col=-1;
SELECT 
    `id`, 
    `date`,
    IF(@col!=`id`,@num:=1,@num:=@num+1),
    @col:=id
 FROM 
     t 
 ORDER BY `id`, `date` ASC
 
 

---

表

CREATE TABLE `t` (
  `id` int(32) NOT NULL,
  `date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `t`(`id`,`date`) values (1,'2016-01-20 15:34:54'),(1,'2016-01-19 15:36:27'),(1,'2016-01-04 15:36:34'),(1,'2016-01-06 15:36:38'),(2,'2016-01-11 15:36:43'),(2,'2016-01-04 15:36:49'),(2,'2016-01-30 15:36:54');

查询语句
这个语句效率很低，生产环境建议不要使用。本着学习的精神，完成你的需求。

SELECT t1.*,COUNT(t1.n) FROM 
    (SELECT @num:=@num+1 AS n,t.*  FROM (SELECT @num:=0) n,t  ORDER BY t.id ASC,DATE ASC ) AS t1 
LEFT JOIN
    (SELECT @f:=@f+1 AS m,t.*  FROM (SELECT @f:=0) n,t  ORDER BY t.id ASC,DATE ASC ) AS t2
ON 
    t1.id = t2.id
WHERE 
    t1.n>=t2.m GROUP BY t1.n