求一SQL 語句

[ghostxyz0]

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

[ACMAIN_CHM]

mysql> select * from pay2;
+------------+------+
| 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>

[vipper23]

mysql> select aa.date,ifnull(bb.num,0) from  (select distinct date from pay2) aa left join (select date,count(uid) num from (select * from pay2 t where not  exists (select 1 from pay2 where uid=t.uid
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)

[wwwwb]

SELECT b2.`DATE`, IFNULL(MAX(ma),0) FROM pay2 b2
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`

[lhlove271015]

[Quote=引用 1 楼 acmain_chm 的回复:]
SQL code
mysql> select * from pay2;
+------------+------+
| date | uid |
+------------+------+
| 2010-04-01 | 1 |
| 2010-04-01 | 2 |
| 2010-04-02 | 1 |
| 2010-04-02 | 3 |
|……
[/Quote]
正解

[shenshine]

恩，先根据uid过滤重复,然后再按日期分组就能得到你要的结果了