关于分组

yueliangdao0608

原帖由 喃喃 于 2008-1-16 09:15 发表
呵呵，可以这样实现

SELECT a.employee_id,
       a.department_id,
       a.salary
  FROM employees a
WHERE (SELECT COUNT(*)
          FROM employees b
         WHERE b.department_id = a.department_id
           AND b.salary > a.salary) < 3
ORDER BY a.department_id,
          a.salary DESC

这个很猛。

yueliangdao0608

原帖由 喃喃 于 2008-1-16 11:02 发表


不是我想出来的，是看了别人的帖子
也不是mysql特有的，是标准SQL

对了。你是在哪里看得。相关子查询的效率也是挺高的。刚次只是粗略的看了一下。

yueliangdao0608

看来还得继续努力学习。上次我记得我给人家写了一个SP。没想到这个SQL语句就能实现了。

yueliangdao0608

不过效率还是没有存储过程来的高

我的测试结果：
mysql> CREATE TABLE `t` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `g_id` int(11) NOT NULL,
    ->   `t_str` varchar(255) NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> /*Data for the table `t` */
mysql>
mysql> insert  into `t`(`id`,`g_id`,`t_str`) values (1,2,'wo'),(2,2,'ni'),(3,2,'ta'),(4,3,'wo '),(5,4,'ni'),(6,3,'ni'),(7,4,'ta'),(8,3,'wang'),(9,4,'li'),(10,3,'hai'),(11,4,'ri'),(12,2,'ren'),(13,5,'ta'),(14,6,'ri'),(15,6,'ren'),(16,6,'fuck'),(17,6,'shit'),(18,5,'ls'),(19,5,'chmod'),(20,5,'chgrp'),(21,5,'chown'),(22,3,'rm'),(23,3,'desc'),(24,4,'pwd'),(25,5,'cd');
Query OK, 25 rows affected (0.00 sec)
Records: 25  Duplicates: 0  Warnings: 0

mysql> create index f_g_id on t(g_id);
Query OK, 25 rows affected (0.04 sec)
Records: 25  Duplicates: 0  Warnings: 0

mysql> desc t;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| g_id  | int(11)      | NO   | MUL |         |                |
| t_str | varchar(255) | NO   |     |         |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from t;
+----+------+-------+
| id | g_id | t_str |
+----+------+-------+
|  1 |    2 | wo    |
|  2 |    2 | ni    |
|  3 |    2 | ta    |
|  4 |    3 | wo    |
|  5 |    4 | ni    |
|  6 |    3 | ni    |
|  7 |    4 | ta    |
|  8 |    3 | wang  |
|  9 |    4 | li    |
| 10 |    3 | hai   |
| 11 |    4 | ri    |
| 12 |    2 | ren   |
| 13 |    5 | ta    |
| 14 |    6 | ri    |
| 15 |    6 | ren   |
| 16 |    6 | fuck  |
| 17 |    6 | shit  |
| 18 |    5 | ls    |
| 19 |    5 | chmod |
| 20 |    5 | chgrp |
| 21 |    5 | chown |
| 22 |    3 | rm    |
| 23 |    3 | desc  |
| 24 |    4 | pwd   |
| 25 |    5 | cd    |
+----+------+-------+
25 rows in set (0.00 sec)

mysql> SELECT a.* FROM t a WHERE
    -> (
    -> SELECT COUNT(*) FROM t b WHERE a.g_id = b.g_id AND b.id<a.id
    -> ) < 2 ORDER BY a.g_id DESC;
+----+------+-------+
| id | g_id | t_str |
+----+------+-------+
| 15 |    6 | ren   |
| 14 |    6 | ri    |
| 18 |    5 | ls    |
| 13 |    5 | ta    |
|  7 |    4 | ta    |
|  5 |    4 | ni    |
|  6 |    3 | ni    |
|  4 |    3 | wo    |
|  2 |    2 | ni    |
|  1 |    2 | wo    |
+----+------+-------+
10 rows in set (0.04 sec)

mysql>
mysql> explain SELECT a.* FROM t a WHERE
    -> (
    -> SELECT COUNT(*) FROM t b WHERE a.g_id = b.g_id AND b.id<a.id
    -> ) < 2 ORDER BY a.g_id DESC\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: index
possible_keys: NULL
          key: f_g_id
      key_len: 4
          ref: NULL
         rows: 25
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: b
         type: ref
possible_keys: PRIMARY,f_g_id
          key: f_g_id
      key_len: 4
          ref: test.a.g_id
         rows: 2
        Extra: Using where; Using index
2 rows in set (0.00 sec)

yueliangdao0608

写了 一个。数据太少。看不出来效率如何。

1. 
   
2. DELIMITER $$
   
3. 
   
4. DROP PROCEDURE IF EXISTS `test`.`sp_get_num_group`$$
   
5. 
   
6. CREATE PROCEDURE `test`.`sp_get_num_group`(
   
7. IN f_num int)
   
8. BEGIN
   
9.   -- The variable stands for totla number of the record.
   
10.   declare cnt int default 0;
    
11.   declare i int;
    
12.   -- Create temp table to reserved the result.
    
13.   create temporary table if not exists tmp select * from t where 1 = 0;
    
14.   -- Get the total number of the group by record.
    
15.   select count(*) from
    
16.   (
    
17.     select count(*) from t group by g_id order by null
    
18.   ) T into cnt;
    
19.   set i = 0;
    
20.   while i < cnt
    
21.   do
    
22.     -- Get the real g_id one by one.
    
23.     set @stmt = concat('select g_id from t group by g_id order by g_id desc limit ',i,',1 into @tmp_id');
    
24.     prepare s1 from @stmt;
    
25.     execute s1;
    
26.     drop prepare s1;
    
27.     set @stmt = '';
    
28.     -- Get the needed data.
    
29.     set @stmt = concat('insert into tmp select * from t where g_id = ',@tmp_id,' order by g_id desc limit ',f_num);
    
30.     prepare s1 from @stmt;
    
31.     execute s1;
    
32.     drop prepare s1;
    
33.     set @stmt = '';
    
34.     set i = i + 1;
    
35.   end while;
    
36.   -- Get the record from temp table.
    
37.   select * from tmp;
    
38.   -- Drop temp table.
    
39.   drop table tmp;
    
40. END$$
    
41. 
    
42. DELIMITER ;

复制代码