请教一条mysql复合查询语句

表 user 和 item 表结构及数据如下: 
------------------------------------------ 
CREATE TABLE `user` ( 
  `userid` smallint(6) unsigned NOT NULL auto_increment, 
  `username` varchar(20) NOT NULL default '', 
  PRIMARY KEY  (`userid`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; 

INSERT INTO `user` VALUES (1, 'user1'); 
INSERT INTO `user` VALUES (2, 'user2'); 
INSERT INTO `user` VALUES (3, 'user3'); 
INSERT INTO `user` VALUES (4, 'user4'); 
INSERT INTO `user` VALUES (5, 'user5'); 

CREATE TABLE `item` ( 
  `itemid` int(11) unsigned NOT NULL auto_increment, 
  `userid` smallint(6) unsigned NOT NULL default '0', 
  `content` varchar(100) NOT NULL default '', 
  `addtime` datetime NOT NULL default '0000-00-00 00:00:00', 
  PRIMARY KEY  (`itemid`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ; 

INSERT INTO `item` VALUES (1, 1, 'content1', '2009-08-12 10:00:00'); 
INSERT INTO `item` VALUES (2, 1, 'content2', '2009-08-12 12:00:00'); 
INSERT INTO `item` VALUES (3, 2, 'content3', '2009-08-12 10:01:00'); 
INSERT INTO `item` VALUES (4, 2, 'content4', '2009-08-12 10:10:00'); 
INSERT INTO `item` VALUES (5, 2, 'content5', '2009-08-12 10:30:10'); 
INSERT INTO `item` VALUES (6, 4, 'content4', '2009-08-29 17:36:56'); 
INSERT INTO `item` VALUES (7, 5, 'content122', '2009-08-29 17:37:05'); 
INSERT INTO `item` VALUES (8, 5, 'content12', '2009-08-29 17:37:12'); 
INSERT INTO `item` VALUES (9, 5, 'content32', '2009-08-29 17:37:22'); 
INSERT INTO `item` VALUES (10, 5, 'content33', '2009-08-29 17:37:29'); 
INSERT INTO `item` VALUES (11, 5, 'content31', '2009-08-29 17:37:35'); 
INSERT INTO `item` VALUES (12, 3, 'content22', '2009-08-29 17:37:42'); 
INSERT INTO `item` VALUES (13, 3, 'content21', '2009-08-29 17:37:46'); 
INSERT INTO `item` VALUES (14, 3, 'content23', '2009-08-29 17:37:50'); 
INSERT INTO `item` VALUES (15, 3, 'content25', '2009-08-29 17:37:54'); 
INSERT INTO `item` VALUES (16, 3, 'content26', '2009-08-29 17:37:57'); 
INSERT INTO `item` VALUES (17, 4, 'content46', '2009-08-29 17:38:03'); 
INSERT INTO `item` VALUES (18, 4, 'content41', '2009-08-29 17:38:07'); 
INSERT INTO `item` VALUES (19, 4, 'content45', '2009-08-29 17:38:10'); 
INSERT INTO `item` VALUES (20, 4, 'content43', '2009-08-29 17:38:14'); 
INSERT INTO `item` VALUES (21, 4, 'content48', '2009-08-29 17:38:17'); 
INSERT INTO `item` VALUES (22, 4, 'content49', '2009-08-29 17:38:21'); 
INSERT INTO `item` VALUES (23, 4, 'content40', '2009-08-29 17:38:28'); 
INSERT INTO `item` VALUES (24, 2, 'content21', '2009-08-29 17:38:42'); 
INSERT INTO `item` VALUES (25, 2, 'content28', '2009-08-29 17:38:46'); 
INSERT INTO `item` VALUES (26, 2, 'content88', '2009-08-29 17:38:51'); 
INSERT INTO `item` VALUES (27, 1, 'content9', '2009-08-29 17:39:08'); 
INSERT INTO `item` VALUES (28, 1, 'content7', '2009-08-29 17:39:12'); 
INSERT INTO `item` VALUES (29, 1, 'content8', '2009-08-29 17:39:17'); 
INSERT INTO `item` VALUES (30, 1, 'content5', '2009-08-29 17:39:21'); 
INSERT INTO `item` VALUES (31, 3, 'content65', '2009-08-29 17:39:27'); 
INSERT INTO `item` VALUES (32, 4, 'content652', '2009-08-29 17:39:34'); 
INSERT INTO `item` VALUES (33, 4, 'content622', '2009-08-29 17:39:39'); 
INSERT INTO `item` VALUES (34, 5, 'content122', '2009-08-29 17:39:46'); 


现需要一条复合语名同时查询出所有资料,每一个用户名,只取出一条对应 item 里的内容

php 和 mysql 本是一家,这里牛人多,人气也高,所以想着发来这里

5 个解决方案

#1

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>

建议你列出你的表结构，并提供测试数据以及 基于这些测试数据的所对应正确结果。


问题说明越详细，回答也会越准确！参见如何提问。（ 提问的智慧）

#2

----------------------------
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)


--------------------------------------------------------------
返回数据就是这样子,这语句可能完成这样的功能,但如果数据表很大,并且查询到数据量大时,需要时间太长

不知道能不能优化一下,或者有其它的代替语句?

#3

主要是:
select userid,max(itemid) as max_itemid from item group by userid
这一句查询到的数据量大, 就算加上 where ,数据量达到 2W 条,这时候再 group by ,需要时间太长,占用资源大

#4

select u.*,it.* from user u Left Join item as it ON it.userid=u.userid

#5

楼上是对的，用left join