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

一时失言乱红尘 2022-08-05 18:28 98阅读 0赞

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)