SELECT COUNT( * ) AS count , d.uid,GROUP_CONCAT(d.day) FROM ( SELECT a.uid, a.time, a.day FROM ceshi a RIGHT JOIN ( SELECT DAY , min( time ) AS min FROM ceshi GROUP BY DAY )m ON ( a.day = m.day AND a.time = m.min ) ORDER BY DAY ASC )d GROUP BY d.uid ORDER BY count DESC我的笨办法
select uid, count(uid), GROUP_CONCAT(day) from ceshi c1 where time = (select MIN(`time`) from ceshi c2 where c2.day = c1.day) group by uidselect uid, count(uid), GROUP_CONCAT(day) from ceshi c1 where time = (select MIN(`time`) from ceshi c2 where c2.day = c1.day) and MONTH(day) = 11 and YEAR(day) = 2007 group by uid 不支持子查询的mysql版本就不能这么做了。
DROP PROCEDURE IF EXISTS p; DELIMITER // CREATE PROCEDURE p () BEGIN DECLARE now DATE; DECLARE days CURSOR FOR SELECT day FROM ceshi GROUP BY day; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET @flag_days=true; DROP TABLE IF EXISTS tmptb; CREATE TEMPORARY TABLE tmptb (uid INT, times INT NOT NULL DEFAULT 1, PRIMARY KEY (uid)) ENGINE=MEMORY; SET @flag_days=false; OPEN days; WHILE @flag_days=false DO FETCH days INTO now; IF @flag_days=false THEN SET @now=now; BEGIN DECLARE user INT; DECLARE uids CURSOR FOR SELECT uid FROM ceshi WHERE day=@now ORDER BY time; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET @flag_uids=true; SET @flag_uids=false; OPEN uids; WHILE @flag_uids=false DO FETCH uids INTO user; IF @flag_uids=false THEN IF user IS NOT null THEN INSERT INTO tmptb SET uid=user ON DUPLICATE KEY UPDATE times=times+1; END IF; END IF; END WHILE; END; END IF; END WHILE; SELECT * FROM tmptb; END;// DELIMITER ; 测试结果:mysql> call p(); +-----+-------+ | uid | times | +-----+-------+ | 1 | 2 | | 2 | 1 | | 3 | 1 | +-----+-------+ 3 rows in set (0.00 sec)
修改下: DROP PROCEDURE IF EXISTS p; DELIMITER // CREATE PROCEDURE p () BEGIN DECLARE now DATE; DECLARE days CURSOR FOR SELECT day FROM ceshi GROUP BY day; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET @flag_days=true; DROP TABLE IF EXISTS tmptb; CREATE TEMPORARY TABLE tmptb (uid INT, times INT NOT NULL DEFAULT 1, PRIMARY KEY (uid)) ENGINE=MEMORY; SET @flag_days=false; OPEN days; WHILE @flag_days=false DO FETCH days INTO @now; IF @flag_days=false THEN SET @now=now; BEGIN DECLARE user INT; DECLARE uids CURSOR FOR SELECT uid FROM ceshi WHERE day=@now ORDER BY time; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET @flag_uids=true; SET @flag_uids=false; OPEN uids; WHILE @flag_uids=false DO FETCH uids INTO user; IF @flag_uids=false THEN INSERT INTO tmptb SET uid=user ON DUPLICATE KEY UPDATE times=times+1; END IF; END WHILE; END; END IF; END WHILE; SELECT * FROM tmptb; END;// DELIMITER ;
时间为 time 是unix时间戳
人物姓名为 uid表示
日期 为 day
FROM (
SELECT a.uid, a.time, a.day
FROM ceshi a
RIGHT JOIN (
SELECT DAY , min( time ) AS min
FROM ceshi
GROUP BY DAY
)m ON ( a.day = m.day
AND a.time = m.min )
ORDER BY DAY ASC
)d
GROUP BY d.uid
ORDER BY count DESC我的笨办法
select uid, count(uid), GROUP_CONCAT(day)
from ceshi c1
where time = (select MIN(`time`) from ceshi c2 where c2.day = c1.day)
group by uidselect uid, count(uid), GROUP_CONCAT(day)
from ceshi c1
where time = (select MIN(`time`) from ceshi c2 where c2.day = c1.day) and MONTH(day) = 11 and YEAR(day) = 2007
group by uid
不支持子查询的mysql版本就不能这么做了。
如果在超越这是删无赦帖子,这是Csdn的悲哀.
DROP PROCEDURE IF EXISTS p;
DELIMITER //
CREATE PROCEDURE p ()
BEGIN
DECLARE now DATE;
DECLARE days CURSOR FOR SELECT day FROM ceshi GROUP BY day;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET @flag_days=true;
DROP TABLE IF EXISTS tmptb;
CREATE TEMPORARY TABLE tmptb (uid INT, times INT NOT NULL DEFAULT 1, PRIMARY KEY (uid)) ENGINE=MEMORY;
SET @flag_days=false;
OPEN days;
WHILE @flag_days=false DO
FETCH days INTO now;
IF @flag_days=false THEN
SET @now=now;
BEGIN
DECLARE user INT;
DECLARE uids CURSOR FOR SELECT uid FROM ceshi WHERE day=@now ORDER BY time;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET @flag_uids=true;
SET @flag_uids=false;
OPEN uids;
WHILE @flag_uids=false DO
FETCH uids INTO user;
IF @flag_uids=false THEN
IF user IS NOT null THEN
INSERT INTO tmptb SET uid=user ON DUPLICATE KEY UPDATE times=times+1;
END IF;
END IF;
END WHILE;
END;
END IF;
END WHILE;
SELECT * FROM tmptb;
END;//
DELIMITER ;
测试结果:mysql> call p();
+-----+-------+
| uid | times |
+-----+-------+
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
+-----+-------+
3 rows in set (0.00 sec)
DROP PROCEDURE IF EXISTS p;
DELIMITER //
CREATE PROCEDURE p ()
BEGIN
DECLARE now DATE;
DECLARE days CURSOR FOR SELECT day FROM ceshi GROUP BY day;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET @flag_days=true;
DROP TABLE IF EXISTS tmptb;
CREATE TEMPORARY TABLE tmptb (uid INT, times INT NOT NULL DEFAULT 1, PRIMARY KEY (uid)) ENGINE=MEMORY;
SET @flag_days=false;
OPEN days;
WHILE @flag_days=false DO
FETCH days INTO @now;
IF @flag_days=false THEN
SET @now=now;
BEGIN
DECLARE user INT;
DECLARE uids CURSOR FOR SELECT uid FROM ceshi WHERE day=@now ORDER BY time;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET @flag_uids=true;
SET @flag_uids=false;
OPEN uids;
WHILE @flag_uids=false DO
FETCH uids INTO user;
IF @flag_uids=false THEN
INSERT INTO tmptb SET uid=user ON DUPLICATE KEY UPDATE times=times+1;
END IF;
END WHILE;
END;
END IF;
END WHILE;
SELECT * FROM tmptb;
END;//
DELIMITER ;