mysql分组排序陷阱_mysql group 陷阱

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