SET FOREIGN_KEY_CHECKS=0;
----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`classid` int(11) NOT NULL,
`classname` char(64) COLLATE utf8_unicode_ci NOT NULL,
`arpic` char(255) COLLATE utf8_unicode_ci NOT NULL,
`status` tinyint(1) NOT NULL,
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`classid`),
KEY `idx_cid` (`classid`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `class` VALUES ('1', '诗歌', '6.jpg', '1', '2011-08-25 09:57:35');
INSERT INTO `class` VALUES ('2', '散文', '5.jpg', '1', '2011-08-25 09:57:59');
INSERT INTO `class` VALUES ('3', '故事小说', '4.jpg', '1', '2011-08-25 09:58:12');
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for blog
-- ----------------------------
DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`uname` char(64) NOT NULL,
`classid` int(10) NOT NULL,
`subject` char(128) COLLATE utf8_unicode_ci NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `blog` VALUES ('10', 'xiao ming', '1', '静夜思', '100');
INSERT INTO `blog` VALUES ('11', 'haha', '1', '回家', '98');
INSERT INTO `blog` VALUES ('12', 'heihei', '1', '倔强', '50');
INSERT INTO `blog` VALUES ('13', 'hello', '2', '你好', '49');要求结果是:
classid classname count(blog) uid(得分最高的uid) uname(得分最高的名字) subject(得分最高的主题)
1 诗歌 3 10 xiao ming 静夜思
2 散文 1 13 hello 你好
3 故事小说 0 请教大家这样的怎么写sql呀。
+---------+-----------+-------+--------+---------------------+
| classid | classname | arpic | status | ctime |
+---------+-----------+-------+--------+---------------------+
| 1 | 诗歌 | 6.jpg | 1 | 2011-08-25 09:57:35 |
| 2 | 散文 | 5.jpg | 1 | 2011-08-25 09:57:59 |
| 3 | 故事小说 | 4.jpg | 1 | 2011-08-25 09:58:12 |
+---------+-----------+-------+--------+---------------------+
3 rows in set (0.00 sec)mysql> select * from blog;
+----+-----+-----------+---------+---------+-------+
| id | uid | uname | classid | subject | score |
+----+-----+-----------+---------+---------+-------+
| 1 | 10 | xiao ming | 1 | 静夜思 | 100 |
| 2 | 11 | haha | 1 | 回家 | 98 |
| 3 | 12 | heihei | 1 | 倔强 | 50 |
| 4 | 13 | hello | 2 | 你好 | 49 |
+----+-----+-----------+---------+---------+-------+
4 rows in set (0.00 sec)mysql> select c.classid ,c.classname , t.cnt, t.uid,t.uname,t.subject
-> from class c left join (select classid,uid,uname,subject,count(*) as cnt from (select * from blog order by classid,score desc ) t1 group by classid) t on c.classid=t.classid;
+---------+-----------+------+------+-----------+---------+
| classid | classname | cnt | uid | uname | subject |
+---------+-----------+------+------+-----------+---------+
| 1 | 诗歌 | 3 | 10 | xiao ming | 静夜思 |
| 2 | 散文 | 1 | 13 | hello | 你好 |
| 3 | 故事小说 | NULL | NULL | NULL | NULL |
+---------+-----------+------+------+-----------+---------+
3 rows in set (0.03 sec)mysql>