表 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 ;#
# 导出表中的数据 `user`
#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 里的内容
------------------------------------------
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 ;#
# 导出表中的数据 `user`
#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 里的内容
from user u inner join item i using (userid);
一个普通查询啊。
或者你期望的正确结果是什么?
mysql> select * from user;
+--------+----------+
| userid | username |
+--------+----------+
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
| 4 | user4 |
| 5 | user5 |
+--------+----------+
5 rows in set (0.09 sec)mysql> select * from item;
+--------+--------+------------+---------------------+
| itemid | userid | content | addtime |
+--------+--------+------------+---------------------+
| 1 | 1 | content1 | 2009-08-12 10:00:00 |
| 2 | 1 | content2 | 2009-08-12 12:00:00 |
| 3 | 2 | content3 | 2009-08-12 10:01:00 |
| 4 | 2 | content4 | 2009-08-12 10:10:00 |
| 5 | 2 | content5 | 2009-08-12 10:30:10 |
| 6 | 4 | content4 | 2009-08-29 17:36:56 |
| 7 | 5 | content122 | 2009-08-29 17:37:05 |
| 8 | 5 | content12 | 2009-08-29 17:37:12 |
| 9 | 5 | content32 | 2009-08-29 17:37:22 |
| 10 | 5 | content33 | 2009-08-29 17:37:29 |
| 11 | 5 | content31 | 2009-08-29 17:37:35 |
| 12 | 3 | content22 | 2009-08-29 17:37:42 |
| 13 | 3 | content21 | 2009-08-29 17:37:46 |
| 14 | 3 | content23 | 2009-08-29 17:37:50 |
| 15 | 3 | content25 | 2009-08-29 17:37:54 |
| 16 | 3 | content26 | 2009-08-29 17:37:57 |
| 17 | 4 | content46 | 2009-08-29 17:38:03 |
| 18 | 4 | content41 | 2009-08-29 17:38:07 |
| 19 | 4 | content45 | 2009-08-29 17:38:10 |
| 20 | 4 | content43 | 2009-08-29 17:38:14 |
| 21 | 4 | content48 | 2009-08-29 17:38:17 |
| 22 | 4 | content49 | 2009-08-29 17:38:21 |
| 23 | 4 | content40 | 2009-08-29 17:38:28 |
| 24 | 2 | content21 | 2009-08-29 17:38:42 |
| 25 | 2 | content28 | 2009-08-29 17:38:46 |
| 26 | 2 | content88 | 2009-08-29 17:38:51 |
| 27 | 1 | content9 | 2009-08-29 17:39:08 |
| 28 | 1 | content7 | 2009-08-29 17:39:12 |
| 29 | 1 | content8 | 2009-08-29 17:39:17 |
| 30 | 1 | content5 | 2009-08-29 17:39:21 |
| 31 | 3 | content65 | 2009-08-29 17:39:27 |
| 32 | 4 | content652 | 2009-08-29 17:39:34 |
| 33 | 4 | content622 | 2009-08-29 17:39:39 |
| 34 | 5 | content122 | 2009-08-29 17:39:46 |
+--------+--------+------------+---------------------+
34 rows in set (0.00 sec)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>
对于同一个userid,你想取表item中id值最大的那条还是最小的那条?
表item中id值最小的那条:
select a.*,b.* from item a, `user` b where not exists (select 1 from item c where c.userid=a.userid and c.itemid<a.itemid) and a.userid=b.userid;表item中id值最大的那条:
select a.*,b.* from item a, `user` b where not exists (select 1 from item c where c.userid=a.userid and c.itemid>a.itemid) and a.userid=b.userid;
+--------+--------+------------+---------------------+
| itemid | userid | content | addtime |
+--------+--------+------------+---------------------+
| 1 | 1 | content1 | 2009-08-12 10:00:00 |
| 27 | 1 | content9 | 2009-08-29 17:39:08 |
| 28 | 1 | content7 | 2009-08-29 17:39:12 |
| 29 | 1 | content8 | 2009-08-29 17:39:17 |
| 30 | 1 | content5 | 2009-08-29 17:39:21 |
| 2 | 1 | content2 | 2009-08-12 12:00:00 |
| 26 | 2 | content88 | 2009-08-29 17:38:51 |
| 24 | 2 | content21 | 2009-08-29 17:38:42 |
| 5 | 2 | content5 | 2009-08-12 10:30:10 |
| 4 | 2 | content4 | 2009-08-12 10:10:00 |
| 3 | 2 | content3 | 2009-08-12 10:01:00 |
| 25 | 2 | content28 | 2009-08-29 17:38:46 |
| 16 | 3 | content26 | 2009-08-29 17:37:57 |
| 15 | 3 | content25 | 2009-08-29 17:37:54 |
| 13 | 3 | content21 | 2009-08-29 17:37:46 |
| 12 | 3 | content22 | 2009-08-29 17:37:42 |
| 31 | 3 | content65 | 2009-08-29 17:39:27 |
| 14 | 3 | content23 | 2009-08-29 17:37:50 |
| 33 | 4 | content622 | 2009-08-29 17:39:39 |
| 32 | 4 | content652 | 2009-08-29 17:39:34 |
| 23 | 4 | content40 | 2009-08-29 17:38:28 |
| 22 | 4 | content49 | 2009-08-29 17:38:21 |
| 21 | 4 | content48 | 2009-08-29 17:38:17 |
| 20 | 4 | content43 | 2009-08-29 17:38:14 |
| 19 | 4 | content45 | 2009-08-29 17:38:10 |
| 18 | 4 | content41 | 2009-08-29 17:38:07 |
| 17 | 4 | content46 | 2009-08-29 17:38:03 |
| 6 | 4 | content4 | 2009-08-29 17:36:56 |
| 7 | 5 | content122 | 2009-08-29 17:37:05 |
| 8 | 5 | content12 | 2009-08-29 17:37:12 |
| 9 | 5 | content32 | 2009-08-29 17:37:22 |
| 10 | 5 | content33 | 2009-08-29 17:37:29 |
| 11 | 5 | content31 | 2009-08-29 17:37:35 |
| 34 | 5 | content122 | 2009-08-29 17:39:46 |
+--------+--------+------------+---------------------+
34 rows in set (0.00 sec)
返回的内容就是这个样子,这语句可以查询到,但如果数据表很大,并且查询到的数据量大时,速度很慢!
数据库版本是: MySQL 4.1.12不知道还有没有办法优化一下?
select userid,max(itemid) as max_itemid from item group by userid
这一句查询到的数据量大, 就算加上 where ,数据量达到 2W 条,这时候再 group by ,需要时间太长,占用资源大
但也是占用时间太长了,每个 userid 对应的 itemit 里是几百条记录效率比较不上2楼
那当然啦
你表item里面都没有userid的索引,你试下对表itme建立索引(userid,itemid)再看看效果吧