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
解决方案 »
- Mysql5.5怎样编译集成infobright引擎?
- 怎么样把哪些数据与当前时间对比之后再排序?
- 自助建站数据库该怎么设计?
- 怎么把year(curdate())转换成字符串
- Mysql 的rank 函数实现的问题
- 求助!XAMPP for Linux 1.7.4,关于mysql一个问题
- mysql关联查询语句如何写?
- 怎样才能让mysql的id自动增量从1开始.
- MYSQL4.0的数据怎么导入4.1版本
- show full processlist 出现大量的mysql-connector-java-
- 高密度执行的sql(返回数据5万)优化?
- 请问,win下 Postgresql8.3如何让非本机用户访问?
+------------+------+
| 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>
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`