CREATE TABLE `pay2` (
`date` varchar(11) NOT NULL default '0',
`uid` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `pay2` VALUES ('2010-04-01','1');
INSERT INTO `pay2` VALUES ('2010-04-01','2');
INSERT INTO `pay2` VALUES ('2010-04-02','1');
INSERT INTO `pay2` VALUES ('2010-04-02','3');
INSERT INTO `pay2` VALUES ('2010-04-03','5');
INSERT INTO `pay2` VALUES ('2010-04-03','2');
INSERT INTO `pay2` VALUES ('2010-04-04','3');
INSERT INTO `pay2` VALUES ('2010-04-05','4');
INSERT INTO `pay2` VALUES ('2010-04-04','4');uid 是用戶ID
現要統計每天新增的用戶數,
要達到的結果是
date num
2010-04-01 2
2010-04-02 1
2010-04-03 1
2010-04-04 1
2010-04-05 0求SQL
+------------+------+
| date | uid |
+------------+------+
| 2010-04-01 | 1 |
| 2010-04-01 | 2 |
| 2010-04-02 | 1 |
| 2010-04-02 | 3 |
| 2010-04-03 | 5 |
| 2010-04-03 | 2 |
| 2010-04-04 | 3 |
| 2010-04-05 | 4 |
| 2010-04-04 | 4 |
+------------+------+
9 rows in set (0.00 sec)mysql> select m_day,count(*) from (select uid,min(date) as m_day from pay2 group
by uid) t group by m_day;
+------------+----------+
| m_day | count(*) |
+------------+----------+
| 2010-04-01 | 2 |
| 2010-04-02 | 1 |
| 2010-04-03 | 1 |
| 2010-04-04 | 1 |
+------------+----------+
4 rows in set (0.00 sec)mysql>
and date<t.date)) a group by date) bb on aa.date=bb.date;
+------------+------------------+
| date | ifnull(bb.num,0) |
+------------+------------------+
| 2010-04-01 | 2 |
| 2010-04-02 | 1 |
| 2010-04-03 | 1 |
| 2010-04-04 | 1 |
| 2010-04-05 | 0 |
+------------+------------------+
5 rows in set (0.00 sec)
LEFT JOIN
(SELECT a.date,a.uid,COUNT(a.uid) AS ma
FROM pay2 a
WHERE NOT EXISTS(SELECT 1 FROM pay2 WHERE a.uid=uid AND a.date>`date` )
GROUP BY a.`date`) b1
ON b1.date=b2.date
GROUP BY b2.`DATE`