有一张表里有两个字段tid uid
1 3
2 5
3 3
4 3
5 6
6 7
7 5
8 7
9 6我要查出每个uid中最大的tid记录后按tid排序 即查出下面的结果列表.tid uid
9 6
8 7
7 5
4 3
请教这条MYSQL语句怎么写
1 3
2 5
3 3
4 3
5 6
6 7
7 5
8 7
9 6我要查出每个uid中最大的tid记录后按tid排序 即查出下面的结果列表.tid uid
9 6
8 7
7 5
4 3
请教这条MYSQL语句怎么写
from table_name
group by uid
order by max_tid desc
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| a1 | CREATE TABLE `a1` (
`tid` int(11) NOT NULL,
`uid` int(11) NOT NULL,
UNIQUE KEY `tid` (`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> select * from a1;
+-----+-----+
| tid | uid |
+-----+-----+
| 1 | 3 |
| 2 | 5 |
| 3 | 3 |
| 4 | 3 |
| 5 | 6 |
| 6 | 7 |
| 7 | 5 |
| 8 | 7 |
| 9 | 6 |
+-----+-----+
9 rows in set (0.00 sec)
mysql> select uid, max(tid) as max_tid
-> from a1
-> group by uid
-> order by max_tid desc
-> ;
+-----+---------+
| uid | max_tid |
+-----+---------+
| 6 | 9 |
| 7 | 8 |
| 5 | 7 |
| 3 | 4 |
+-----+---------+
4 rows in set (0.00 sec)
tid int,
uid int);
insert into lk7 values
(1,3),
(2,5),
(3,3),
(4,3),
(5,6),
(6,7),
(7,5),
(8,7),
(9,6);
select * from (select * from lk7 order by tid desc) T group by uid order by tid desc;
query result(4 records)
tid uid
9 6
8 7
7 5
4 3