MySQL 里面的Where 和Having和Count 和distinct和Group By对比

来源：互联网发布：可意影音 mac 破解版编辑：程序博客网时间：2024/05/17 16:01

mysql> select accid as uid,date(datetime) AS datetime from game.logLogin GROUP BY accid HAVING datetime='2013-8-20';+---------+------------+| uid     | datetime   |+---------+------------+| 1000010 | 2013-08-20 || 1000012 | 2013-08-20 |+---------+------------+rows in set (0.00 sec)

而实际的例子是

mysql> select accid as uid,date(datetime) AS datetime from game.logLogin HAVING datetime='2013-8-20';+---------+------------+| uid     | datetime   |+---------+------------+| 1000004 | 2013-08-20 || 1000004 | 2013-08-20 || 1000001 | 2013-08-20 || 1000000 | 2013-08-20 || 1000004 | 2013-08-20 || 1000004 | 2013-08-20 || 1000012 | 2013-08-20 || 1000010 | 2013-08-20 || 1000000 | 2013-08-20 || 1000002 | 2013-08-20 || 1000006 | 2013-08-20 || 1000003 | 2013-08-20 || 1000003 | 2013-08-20 || 1000012 | 2013-08-20 || 1000003 | 2013-08-20 ||       0 | 2013-08-20 || 1000012 | 2013-08-20 |+---------+------------+rows in set (0.00 sec)

用大腿想都会不对

mysql> select distinct accid as uid from (select accid,date(datetime) AS datetime from game.logLogin HAVING datetime='2013-8-20') as t;+---------+| uid     |+---------+| 1000004 || 1000001 || 1000000 || 1000012 || 1000010 || 1000002 || 1000006 || 1000003 ||       0 |+---------+rows in set (0.00 sec)

当然如何不用 HAVING 和 DISTINCT 和 COUNT 还有GROUP By 的话是可以找出记录的

mysql> select accountID as uid,date(signTime) AS signTime from platform.account HAVING signTime='2013-8-20';+---------+------------+| uid     | signTime   |+---------+------------+| 1000013 | 2013-08-20 || 1000014 | 2013-08-20 |+---------+------------+rows in set (0.00 sec)mysql> select accountID as uid,date(signTime) AS signTime from platform.account HAVING signTime='2013-8-19';+---------+------------+| uid     | signTime   |+---------+------------+| 1000000 | 2013-08-19 || 1000001 | 2013-08-19 || 1000002 | 2013-08-19 || 1000003 | 2013-08-19 || 1000004 | 2013-08-19 || 1000005 | 2013-08-19 || 1000006 | 2013-08-19 || 1000007 | 2013-08-19 || 1000008 | 2013-08-19 || 1000009 | 2013-08-19 || 1000010 | 2013-08-19 || 1000011 | 2013-08-19 || 1000012 | 2013-08-19 |+---------+------------+rows in set (0.00 sec)

0 0