21,887
社区成员
发帖
与我相关
我的任务
分享
mysql> select *
-> from user u inner join item i using (userid);
+--------+----------+--------+------------+---------------------+
| userid | username | itemid | content | addtime |
+--------+----------+--------+------------+---------------------+
| 1 | user1 | 1 | content1 | 2009-08-12 10:00:00 |
| 1 | user1 | 2 | content2 | 2009-08-12 12:00:00 |
| 1 | user1 | 27 | content9 | 2009-08-29 17:39:08 |
| 1 | user1 | 28 | content7 | 2009-08-29 17:39:12 |
| 1 | user1 | 29 | content8 | 2009-08-29 17:39:17 |
| 1 | user1 | 30 | content5 | 2009-08-29 17:39:21 |
| 2 | user2 | 3 | content3 | 2009-08-12 10:01:00 |
| 2 | user2 | 4 | content4 | 2009-08-12 10:10:00 |
| 2 | user2 | 5 | content5 | 2009-08-12 10:30:10 |
| 2 | user2 | 24 | content21 | 2009-08-29 17:38:42 |
| 2 | user2 | 25 | content28 | 2009-08-29 17:38:46 |
| 2 | user2 | 26 | content88 | 2009-08-29 17:38:51 |
| 3 | user3 | 12 | content22 | 2009-08-29 17:37:42 |
| 3 | user3 | 13 | content21 | 2009-08-29 17:37:46 |
| 3 | user3 | 14 | content23 | 2009-08-29 17:37:50 |
| 3 | user3 | 15 | content25 | 2009-08-29 17:37:54 |
| 3 | user3 | 16 | content26 | 2009-08-29 17:37:57 |
| 3 | user3 | 31 | content65 | 2009-08-29 17:39:27 |
| 4 | user4 | 6 | content4 | 2009-08-29 17:36:56 |
| 4 | user4 | 17 | content46 | 2009-08-29 17:38:03 |
| 4 | user4 | 18 | content41 | 2009-08-29 17:38:07 |
| 4 | user4 | 19 | content45 | 2009-08-29 17:38:10 |
| 4 | user4 | 20 | content43 | 2009-08-29 17:38:14 |
| 4 | user4 | 21 | content48 | 2009-08-29 17:38:17 |
| 4 | user4 | 22 | content49 | 2009-08-29 17:38:21 |
| 4 | user4 | 23 | content40 | 2009-08-29 17:38:28 |
| 4 | user4 | 32 | content652 | 2009-08-29 17:39:34 |
| 4 | user4 | 33 | content622 | 2009-08-29 17:39:39 |
| 5 | user5 | 7 | content122 | 2009-08-29 17:37:05 |
| 5 | user5 | 8 | content12 | 2009-08-29 17:37:12 |
| 5 | user5 | 9 | content32 | 2009-08-29 17:37:22 |
| 5 | user5 | 10 | content33 | 2009-08-29 17:37:29 |
| 5 | user5 | 11 | content31 | 2009-08-29 17:37:35 |
| 5 | user5 | 34 | content122 | 2009-08-29 17:39:46 |
+--------+----------+--------+------------+---------------------+
34 rows in set (0.08 sec)
mysql>
mysql> select u.*,i.*
-> from user u inner join (select userid,max(itemid) as max_itemid from item group by userid) b on u.userid=b.userid
-> inner join item i on b.max_itemid=i.itemid;
+--------+----------+--------+--------+------------+---------------------+
| userid | username | itemid | userid | content | addtime |
+--------+----------+--------+--------+------------+---------------------+
| 1 | user1 | 30 | 1 | content5 | 2009-08-29 17:39:21 |
| 2 | user2 | 26 | 2 | content88 | 2009-08-29 17:38:51 |
| 3 | user3 | 31 | 3 | content65 | 2009-08-29 17:39:27 |
| 4 | user4 | 33 | 4 | content622 | 2009-08-29 17:39:39 |
| 5 | user5 | 34 | 5 | content122 | 2009-08-29 17:39:46 |
+--------+----------+--------+--------+------------+---------------------+
5 rows in set (0.11 sec)
mysql>