mysql group 陷阱

来源：互联网发布：数据库持久化编辑：程序博客网时间：2024/05/16 01:36
mysql> SELECT     ->             sn,Clientsn,createdtime    ->         FROM    ->             ClientCashDetail    ->         WHERE    ->             createdTime < '2015-12-25 00:00:00'    ->         GROUP BY clientSn    ->         ORDER BY createdtime DESC;+-------+----------+---------------------+| sn    | Clientsn | createdtime         |+-------+----------+---------------------+| 20146 |    12904 | 2015-12-28 18:51:59 || 20143 |      501 | 2015-12-28 17:36:21 || 20141 |     1177 | 2015-12-28 17:12:55 || 20135 |       57 | 2015-12-28 16:47:43 || 20124 |     1407 | 2015-12-28 16:19:15 || 20108 |    12808 | 2015-12-28 16:05:52 || 20107 |      592 | 2015-12-28 16:05:33 || 20102 |    12738 | 2015-12-28 16:03:21 || 20094 |     1570 | 2015-12-28 15:59:33 || 20091 |     2102 | 2015-12-28 15:57:18 || 20086 |     1659 | 2015-12-28 15:50:31 || 20084 |    13006 | 2015-12-28 15:07:03 || 20083 |      512 | 2015-12-28 15:04:51 |mysql> select Clientsn ,count(*)  from ClientCashDetail group by Clientsn order by count(*) ;|       12 |      100 ||      392 |      100 ||      253 |      105 ||      605 |      105 ||       55 |      107 ||       22 |      109 ||      592 |      110 ||       65 |      110 ||       31 |      112 ||       71 |      113 ||       57 |      118 ||      127 |      123 ||        1 |      127 ||       45 |      140 ||      351 |      156 ||       15 |      160 ||       13 |      187 ||     1123 |      198 |+----------+----------+1075 rows in set (0.00 sec)mysql> select sn,Clientsn,createdtime from ClientCashDetail where  createdTime >= '2015-12-24 00:00:00' and Clientsn=1123;+-------+----------+---------------------+| sn    | Clientsn | createdtime         |+-------+----------+---------------------+| 19951 |     1123 | 2015-12-24 14:36:20 || 19995 |     1123 | 2015-12-24 15:11:59 || 19996 |     1123 | 2015-12-24 15:19:45 || 20118 |     1123 | 2015-12-28 16:09:24 |+-------+----------+---------------------+4 rows in set (0.00 sec)mysql>  select sn,Clientsn,createdtime from ClientCashDetail where  createdTime >= '2015-12-24 00:00:00' and Clientsn=1123 group by Clientsn;+-------+----------+---------------------+| sn    | Clientsn | createdtime         |+-------+----------+---------------------+| 19951 |     1123 | 2015-12-24 14:36:20 |+-------+----------+---------------------+1 row in set (0.00 sec)mysql>  select sn,Clientsn,createdtime from ClientCashDetail where  createdTime >= '2015-12-24 00:00:00' and Clientsn=1123 group by Clientsn order by createdtime desc;+-------+----------+---------------------+| sn    | Clientsn | createdtime         |+-------+----------+---------------------+| 19951 |     1123 | 2015-12-24 14:36:20 |+-------+----------+---------------------+1 row in set (0.00 sec)-------------------------------------------------------------------------------------------------------------------------------------------------------------------mysql>  select sn,Clientsn,createdtime from ClientCashDetail where  createdTime >= '2015-12-24 00:00:00' and Clientsn=1123 ;+-------+----------+---------------------+| sn    | Clientsn | createdtime         |+-------+----------+---------------------+| 19951 |     1123 | 2015-12-24 14:36:20 || 19995 |     1123 | 2015-12-24 15:11:59 || 19996 |     1123 | 2015-12-24 15:19:45 || 20118 |     1123 | 2015-12-28 16:09:24 |+-------+----------+---------------------+4 rows in set (0.00 sec)mysql>  select max(sn),Clientsn,max(createdtime) from ClientCashDetail where  createdTime >= '2015-12-24 00:00:00' and Clientsn=1123 group by Clientsn ;+---------+----------+---------------------+| max(sn) | Clientsn | max(createdtime)    |+---------+----------+---------------------+|   20118 |     1123 | 2015-12-28 16:09:24 |+---------+----------+---------------------+1 row in set (0.00 sec)|    12825 | 2015-12-24 12:05:53 ||    12861 | 2015-12-24 11:49:03 ||    12899 | 2015-12-24 09:12:15 ||    12904 | 2015-12-28 19:00:04 ||    12914 | 2015-12-25 13:53:19 ||    12941 | 2015-12-26 01:57:16 ||    13004 | 2015-12-28 10:08:04 ||    13006 | 2015-12-28 16:06:18 |+----------+---------------------+1075 rows in set (0.00 sec)mysql> select ClientSn, max(createdtime) from ClientCashDetail group by ClientSn;select  SUM(accountBalance)  from ( SELECT     a.*FROM    ClientCashDetail a        INNER JOIN    (SELECT         MAX(createdtime) AS mtime    FROM        ClientCashDetail        where createdTime < '2015-12-25 00:00:00'    GROUP BY ClientSn) b ON a.createdtime = b.mtime) c
0 0