Group By 的使用

1.  

   DROP TABLE IF EXISTS `sco`;
   CREATE TABLE `sco`  (
     `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
     `c_id` int(11) NULL DEFAULT NULL,
     `sc` int(255) NULL DEFAULT NULL
   ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
   
   -- ----------------------------
   -- Records of sco
   -- ----------------------------
   INSERT INTO `sco` VALUES ('A', 1, 88);
   INSERT INTO `sco` VALUES ('A', 2, 43);
   INSERT INTO `sco` VALUES ('B', 2, 90);
   INSERT INTO `sco` VALUES ('C', 3, 43);
   INSERT INTO `sco` VALUES ('A', 3, 100);
   INSERT INTO `sco` VALUES ('B', 1, 99);
   
   

   #查询课程的前n名，下面演示的是前两名
   select t1.name,t1.c_id,t1.sc
   from sco t1
   where
   (select count(1)
   from sco t2
   where t1.c_id = t2.c_id and t1.sc<=t2.sc) <=2;

    

   #查询课程的前n名，并且显示排名,下面演示的是前两名
   select t1.name,t1.c_id,t1.sc,count(t1.name) as "排名"
   from sco t1,sco t2
   where t1.c_id = t2.c_id
   and t1.sc <= t2.sc
   GROUP BY t1.name,t1.c_id,t1.sc
   having count(t1.name) <=2
   order by t1.c_id ,t1.sc desc;

    

   #查询第一名 （先分组查出第一名成绩，然后与原表做匹配）

   select name,t1.c_id,t1.sc
   FROM sco t1,
   (select c_id,max(sc) as sc
   from sco
   GROUP BY c_id)as t2
   where t1.c_id = t2.c_id and t1.sc = t2.sc