求一SQL 语句

[ghostxyz0]

有如下订单表, 求查出每个用户首次下单的订单号, 金额 和下单时间 求SQL, 请高手帮忙

CREATE TABLE `test` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增字段',
  `orderid` varchar(32) DEFAULT NULL COMMENT '订单号',
  `uid` smallint(6) DEFAULT NULL COMMENT '订单用户UID',
  `money` decimal(10,2) unsigned DEFAULT NULL COMMENT '订单金额',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单完成时间戳',
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES (1,'9581fc1156b6cc24',1,6,1312459705);
INSERT INTO `test` VALUES (2,'a65a60abe8bc2a63',2,23,1312462748);
INSERT INTO `test` VALUES (3,'a19bedcf22ee3aad',1,15,1312476597);
INSERT INTO `test` VALUES (4,'a5cf44b4985f8a57',2,9,1312537818);
INSERT INTO `test` VALUES (5,'6a8ed9409f5a9673',4,14,1312615274);
INSERT INTO `test` VALUES (6,'4fd8a33f35c4dd4d',1,19,1312581043);
INSERT INTO `test` VALUES (7,'1de9ef6bc4f39555',3,29,1312606256);
INSERT INTO `test` VALUES (8,'e5beacd54cd7df99',4,12,1312559431);
INSERT INTO `test` VALUES (9,'a639da6dce717a1c',3,10,1312618895);
INSERT INTO `test` VALUES (10,'616a8a4249c0a7cb',2,18,1312622509);

[ACMAIN_CHM]

参考下贴中的多种方法

http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html
[征集]分组取最大N条记录方法征集，及散分....

[ACMAIN_CHM]

其中一种解法如下。

select * from test a where not exists (select 1 from test where uid=a.uid and dateline<a.dateline)

[rucypli]

select *
from tb A
where not exits (select 1 from tb A.uid=uid and A.id>id)

[呆到深处自然萌2]

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判断
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 |
+----+------+-------+-------------------------+

呵，不知道是否对你有帮助

[ghostxyz0]

除了 ACMAIN_CHM 写的, 其它都是错的.

不过方法已经掌握了