求一SQL 语句 本帖最后由 ghostxyz0 于 2011-08-06 16:21:17 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 参考下贴中的多种方法http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html[征集]分组取最大N条记录方法征集,及散分.... 其中一种解法如下。select * from test a where not exists (select 1 from test where uid=a.uid and dateline<a.dateline) select *from tb Awhere not exits (select 1 from tb A.uid=uid and A.id>id) mysql> select * from test;+----+------------------+------+-------+------------+| Id | orderid | uid | money | dateline |+----+------------------+------+-------+------------+| 1 | 9581fc1156b6cc24 | 1 | 6.00 | 1312459705 || 2 | a65a60abe8bc2a63 | 2 | 23.00 | 1312462748 || 3 | a19bedcf22ee3aad | 1 | 15.00 | 1312476597 || 4 | a5cf44b4985f8a57 | 2 | 9.00 | 1312537818 || 5 | 6a8ed9409f5a9673 | 4 | 14.00 | 1312615274 || 6 | 4fd8a33f35c4dd4d | 1 | 19.00 | 1312581043 || 7 | 1de9ef6bc4f39555 | 3 | 29.00 | 1312606256 || 8 | e5beacd54cd7df99 | 4 | 12.00 | 1312559431 || 9 | a639da6dce717a1c | 3 | 10.00 | 1312618895 || 10 | 616a8a4249c0a7cb | 2 | 18.00 | 1312622509 |+----+------------------+------+-------+------------+--首次-------------------------------------------------------mysql> select * -> from test t -> where not exists(select 1 from test where t.uid=uid and t.Id>Id);+----+------------------+------+-------+------------+| Id | orderid | uid | money | dateline |+----+------------------+------+-------+------------+| 1 | 9581fc1156b6cc24 | 1 | 6.00 | 1312459705 || 2 | a65a60abe8bc2a63 | 2 | 23.00 | 1312462748 || 5 | 6a8ed9409f5a9673 | 4 | 14.00 | 1312615274 || 7 | 1de9ef6bc4f39555 | 3 | 29.00 | 1312606256 |+----+------------------+------+-------+------------+4 rows in set (0.00 sec)---最近一次-------------------------------------------------------mysql> select * -> from test t -> where not exists(select 1 from test where t.uid=uid and t.Id<Id);+----+------------------+------+-------+------------+| Id | orderid | uid | money | dateline |+----+------------------+------+-------+------------+| 6 | 4fd8a33f35c4dd4d | 1 | 19.00 | 1312581043 || 8 | e5beacd54cd7df99 | 4 | 12.00 | 1312559431 || 9 | a639da6dce717a1c | 3 | 10.00 | 1312618895 || 10 | 616a8a4249c0a7cb | 2 | 18.00 | 1312622509 |+----+------------------+------+-------+------------+4 rows in set (0.00 sec) 一种方法按照自增id判断codeselect id,uid,money,from_unixtime(dateline) from test order by id desc;+----+------+-------+-------------------------+| id | uid | money | from_unixtime(dateline) |+----+------+-------+-------------------------+| 10 | 2 | 18.00 | 2011-08-06 17:21:49 || 9 | 3 | 10.00 | 2011-08-06 16:21:35 || 8 | 4 | 12.00 | 2011-08-05 23:50:31 || 7 | 3 | 29.00 | 2011-08-06 12:50:56 || 6 | 1 | 19.00 | 2011-08-06 05:50:43 || 5 | 4 | 14.00 | 2011-08-06 15:21:14 || 4 | 2 | 9.00 | 2011-08-05 17:50:18 || 3 | 1 | 15.00 | 2011-08-05 00:49:57 || 2 | 2 | 23.00 | 2011-08-04 20:59:08 || 1 | 1 | 6.00 | 2011-08-04 20:08:25 |+----+------+-------+-------------------------+二种方法按照时间排序codeselect id,uid,money,from_unixtime(dateline) from test order by dateline desc;+----+------+-------+-------------------------+| id | uid | money | from_unixtime(dateline) |+----+------+-------+-------------------------+| 10 | 2 | 18.00 | 2011-08-06 17:21:49 || 9 | 3 | 10.00 | 2011-08-06 16:21:35 || 5 | 4 | 14.00 | 2011-08-06 15:21:14 || 7 | 3 | 29.00 | 2011-08-06 12:50:56 || 6 | 1 | 19.00 | 2011-08-06 05:50:43 || 8 | 4 | 12.00 | 2011-08-05 23:50:31 || 4 | 2 | 9.00 | 2011-08-05 17:50:18 || 3 | 1 | 15.00 | 2011-08-05 00:49:57 || 2 | 2 | 23.00 | 2011-08-04 20:59:08 || 1 | 1 | 6.00 | 2011-08-04 20:08:25 |+----+------+-------+-------------------------+呵,不知道是否对你有帮助 除了 ACMAIN_CHM 写的, 其它都是错的.不过方法已经掌握了 MYSQL查询某个范围的数据 请教 schedule 上的内容查询 MYSQL中可以按照两项或者多项的和降序select吗 自增序号如何初始化 给出一个字符串,如何在数据表中查询与之匹配的正则表达式? 请问有处理null值的函数吗?就像sql server的isnull(字段,0). 如何一次清空数据库中所有表的数据,但表结构还保留? 如何根据从表的字段内容更新主表 CURRENT_DATE 日期函数的困惑 mysql如何去除重复的关系对 求一同时出明细和汇总的sql语句 mysql源码windows版的在哪?
[征集]分组取最大N条记录方法征集,及散分....
from tb A
where not exits (select 1 from tb A.uid=uid and A.id>id)
+----+------------------+------+-------+------------+
| Id | orderid | uid | money | dateline |
+----+------------------+------+-------+------------+
| 1 | 9581fc1156b6cc24 | 1 | 6.00 | 1312459705 |
| 2 | a65a60abe8bc2a63 | 2 | 23.00 | 1312462748 |
| 3 | a19bedcf22ee3aad | 1 | 15.00 | 1312476597 |
| 4 | a5cf44b4985f8a57 | 2 | 9.00 | 1312537818 |
| 5 | 6a8ed9409f5a9673 | 4 | 14.00 | 1312615274 |
| 6 | 4fd8a33f35c4dd4d | 1 | 19.00 | 1312581043 |
| 7 | 1de9ef6bc4f39555 | 3 | 29.00 | 1312606256 |
| 8 | e5beacd54cd7df99 | 4 | 12.00 | 1312559431 |
| 9 | a639da6dce717a1c | 3 | 10.00 | 1312618895 |
| 10 | 616a8a4249c0a7cb | 2 | 18.00 | 1312622509 |
+----+------------------+------+-------+------------+--首次-------------------------------------------------------
mysql> select *
-> from test t
-> where not exists(select 1 from test where t.uid=uid and t.Id>Id);
+----+------------------+------+-------+------------+
| Id | orderid | uid | money | dateline |
+----+------------------+------+-------+------------+
| 1 | 9581fc1156b6cc24 | 1 | 6.00 | 1312459705 |
| 2 | a65a60abe8bc2a63 | 2 | 23.00 | 1312462748 |
| 5 | 6a8ed9409f5a9673 | 4 | 14.00 | 1312615274 |
| 7 | 1de9ef6bc4f39555 | 3 | 29.00 | 1312606256 |
+----+------------------+------+-------+------------+
4 rows in set (0.00 sec)---最近一次-------------------------------------------------------
mysql> select *
-> from test t
-> where not exists(select 1 from test where t.uid=uid and t.Id<Id);
+----+------------------+------+-------+------------+
| Id | orderid | uid | money | dateline |
+----+------------------+------+-------+------------+
| 6 | 4fd8a33f35c4dd4d | 1 | 19.00 | 1312581043 |
| 8 | e5beacd54cd7df99 | 4 | 12.00 | 1312559431 |
| 9 | a639da6dce717a1c | 3 | 10.00 | 1312618895 |
| 10 | 616a8a4249c0a7cb | 2 | 18.00 | 1312622509 |
+----+------------------+------+-------+------------+
4 rows in set (0.00 sec)
code
select id,uid,money,from_unixtime(dateline) from test order by id desc;
+----+------+-------+-------------------------+
| id | uid | money | from_unixtime(dateline) |
+----+------+-------+-------------------------+
| 10 | 2 | 18.00 | 2011-08-06 17:21:49 |
| 9 | 3 | 10.00 | 2011-08-06 16:21:35 |
| 8 | 4 | 12.00 | 2011-08-05 23:50:31 |
| 7 | 3 | 29.00 | 2011-08-06 12:50:56 |
| 6 | 1 | 19.00 | 2011-08-06 05:50:43 |
| 5 | 4 | 14.00 | 2011-08-06 15:21:14 |
| 4 | 2 | 9.00 | 2011-08-05 17:50:18 |
| 3 | 1 | 15.00 | 2011-08-05 00:49:57 |
| 2 | 2 | 23.00 | 2011-08-04 20:59:08 |
| 1 | 1 | 6.00 | 2011-08-04 20:08:25 |
+----+------+-------+-------------------------+二种方法按照时间排序
code
select id,uid,money,from_unixtime(dateline) from test order by dateline desc;
+----+------+-------+-------------------------+
| id | uid | money | from_unixtime(dateline) |
+----+------+-------+-------------------------+
| 10 | 2 | 18.00 | 2011-08-06 17:21:49 |
| 9 | 3 | 10.00 | 2011-08-06 16:21:35 |
| 5 | 4 | 14.00 | 2011-08-06 15:21:14 |
| 7 | 3 | 29.00 | 2011-08-06 12:50:56 |
| 6 | 1 | 19.00 | 2011-08-06 05:50:43 |
| 8 | 4 | 12.00 | 2011-08-05 23:50:31 |
| 4 | 2 | 9.00 | 2011-08-05 17:50:18 |
| 3 | 1 | 15.00 | 2011-08-05 00:49:57 |
| 2 | 2 | 23.00 | 2011-08-04 20:59:08 |
| 1 | 1 | 6.00 | 2011-08-04 20:08:25 |
+----+------+-------+-------------------------+呵,不知道是否对你有帮助