请教min(),max()等与group by一起的情况

camham

数据量大的时候感觉用临时表会效果好一些

col.g

用一个INNER JOIN实现，每次只找当前值和最大值及对应的日期，或者只找出当前值和最小值及对应的日期

类似于三楼的解决方法

要达到四楼的效果查询太复杂了，起码目前对我是这样。因此一步一步来吧，分步查询能达到效果，性能也能接受（200多万条记录，每次查询大概在一分钟左右的时候，还可以）

从CSDN的一个回复里学来

我也是感觉可能临时表可能性能方面会好一点

对了，主索引是work-id 和work-date组合

select t.work-id,t.work-date,t.prod-num,ma.max_pp,ttt.cur-prod-num,ma.max_pp/ttt.cur-prod-num
from ((select * from table-name where table-name.work-date>'2008-1-1') t inner join
      (select work-id,max(prod-num) as max_pp from table-name where table-name.work-date>'2008-1-1' group by 1 ) ma
          on t.work-id=ma.work-id and t.prod-num=ma.max_pp)
                        inner join
                        (select work-id, prod-num as cur-prod-num from table-name where work-date='2008-2-27') ttt
                                        on t.work-id=ttt.work-id
                                        order by 6

[ 本帖最后由 col.g 于 2008-2-28 22:45 编辑 ]

sunnyfun

不至于要写得这么复杂吧，有时候换个角度很容易就解决了：

SELECT a.work_id, a.prod_dat AS min_prod_dat, c.min_prod_num, b.prod_dat AS max_prod_dat, c.max_prod_num FROM (     SELECT work_id, min( prod_num ) AS min_prod_num, max( prod_num ) AS max_prod_num     FROM table_name     GROUP BY work_id )c JOIN table_name a ON a.work_id = c.work_id AND a.prod_num = c.min_prod_num JOIN table_name b ON b.work_id = c.work_id AND b.prod_num = c.max_prod_num

yueliangdao0608

原帖由 sunnyfun 于 2008-2-29 10:02 发表
不至于要写得这么复杂吧，有时候换个角度很容易就解决了：

SELECT a.work_id, a.prod_dat AS min_prod_dat, c.min_prod_num, b.prod_dat AS max_prod_dat, c.max_prod_num
FROM (
    SELECT work_id, min ...

sunnyfun兄弟的SQL语句很猛。

yueliangdao0608

原帖由 col.g 于 2008-2-28 22:43 发表
用一个INNER JOIN实现，每次只找当前值和最大值及对应的日期，或者只找出当前值和最小值及对应的日期

类似于三楼的解决方法

要达到四楼的效果查询太复杂了，起码目前对我是这样。因此一步一步来吧，分步查 ...

如果要优化的话就加设当的索引。

create index f_u on table_name(work_id,prod_num); explain select a.work_id,a.prod_date as max_date,a.prod_num as max_num,b.prod_date as min_date,b.prod_num as min_num from (select * from table_name as a where a.prod_num = (select max(prod_num) from table_name where work_id = a.work_id )) as a,(select * from table_name as a where a.prod_num = (select min(prod_num) from table_name where work_id = a.work_id )) as b where a.work_id = b.work_id; explain SELECT a.work_id, a.prod_date AS min_prod_dat, c.min_prod_num, b.prod_date AS max_prod_dat, c.max_prod_num FROM (     SELECT work_id, min( prod_num ) AS min_prod_num, max( prod_num ) AS max_prod_num     FROM table_name     GROUP BY work_id )c JOIN table_name a ON a.work_id = c.work_id AND a.prod_num = c.min_prod_num JOIN table_name b ON b.work_id = c.work_id AND b.prod_num = c.max_prod_num

yueliangdao0608

看一下两个EXPLAIN的结果：

mysql> explain
    -> select a.work_id,a.prod_date as max_date,a.prod_num as max_num,b.prod_date as min_date,b.prod_num as min_num
    -> from (select * from table_name as a where a.prod_num = (select max(prod_num) from table_name where work_id = a.work_id
    -> )) as a,(select * from table_name as a where a.prod_num = (select min(prod_num) from table_name where work_id = a.work_id )) as b where a.work_id = b.work_id;
+----+--------------------+------------+------+---------------+------+---------+------------------+------+-------------+
| id | select_type        | table      | type | possible_keys | key  | key_len | ref              | rows | Extra       |
+----+--------------------+------------+------+---------------+------+---------+------------------+------+-------------+
|  1 | PRIMARY            | <derived2> | ALL  | NULL          | NULL | NULL    | NULL             |    2 |             |
|  1 | PRIMARY            | <derived4> | ALL  | NULL          | NULL | NULL    | NULL             |    2 | Using where |
|  4 | DERIVED            | a          | ALL  | NULL          | NULL | NULL    | NULL             |    7 | Using where |
|  5 | DEPENDENT SUBQUERY | table_name | ref  | f_u           | f_u  | 4       | t_girl.a.work_id |    1 | Using index |
|  2 | DERIVED            | a          | ALL  | NULL          | NULL | NULL    | NULL             |    7 | Using where |
|  3 | DEPENDENT SUBQUERY | table_name | ref  | f_u           | f_u  | 4       | t_girl.a.work_id |    1 | Using index |
+----+--------------------+------------+------+---------------+------+---------+------------------+------+-------------+
6 rows in set (0.00 sec)

mysql> explain
    -> SELECT a.work_id, a.prod_date AS min_prod_dat, c.min_prod_num, b.prod_date AS max_prod_dat, c.max_prod_num
    -> FROM (
    ->     SELECT work_id, min( prod_num ) AS min_prod_num, max( prod_num ) AS max_prod_num
    ->     FROM table_name
    ->     GROUP BY work_id
    -> )c
    -> JOIN table_name a ON a.work_id = c.work_id
    -> AND a.prod_num = c.min_prod_num
    -> JOIN table_name b ON b.work_id = c.work_id
    -> AND b.prod_num = c.max_prod_num
    -> ;
+----+-------------+------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL |    2 |             |
|  1 | PRIMARY     | a          | ALL   | f_u           | NULL | NULL    | NULL |    6 | Using where |
|  1 | PRIMARY     | b          | ALL   | f_u           | NULL | NULL    | NULL |    6 | Using where |
|  2 | DERIVED     | table_name | index | NULL          | f_u  | 8       | NULL |    7 | Using index |
+----+-------------+------------+-------+---------------+------+---------+------+------+-------------+
4 rows in set (0.00 sec)

mysql>

col.g

等我慢慢体会一下楼上两位的方法

谢谢谢谢，非常感谢！

sunnyfun

原帖由 yueliangdao0608 于 2008-2-29 10:21 发表
看一下两个EXPLAIN的结果：

mysql> explain
    -> select a.work_id,a.prod_date as max_date,a.prod_num as max_num,b.prod_date as min_date,b.prod_num as min_num
    -> from (select * from tabl ...

不公平啊
人家主索引是work-id 和work-date组合，create index 前先加上

1. ALTER TABLE `table_name` ADD PRIMARY KEY ( `work_id` , `prod_date` ) ;

复制代码

+----+-------------+------------+-------+-----------------+---------+---------+-----------+------+-------------+
| id | select_type | table      | type  | possible_keys   | key     | key_len | ref       | rows | Extra       |
+----+-------------+------------+-------+-----------------+---------+---------+-----------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL            | NULL    |    NULL | NULL      |    2 |             |
|  1 | PRIMARY     | a          | ref   | PRIMARY,work_id | PRIMARY |       4 | c.work_id |    1 | Using where |
|  1 | PRIMARY     | b          | ref   | PRIMARY,work_id | PRIMARY |       4 | c.work_id |    1 | Using where |
|  2 | DERIVED     | table_name | index | NULL            | work_id |       8 | NULL      |    7 | Using index |
+----+-------------+------------+-------+-----------------+---------+---------+-----------+------+-------------+
4 rows in set (0.00 sec)

yueliangdao0608

原帖由 sunnyfun 于 2008-2-29 15:10 发表


不公平啊
人家主索引是work-id 和work-date组合，create index 前先加上ALTER TABLE `table_name` ADD PRIMARY KEY ( `work_id` , `prod_date` ) ;

LZ没有说什么索引。

sunnyfun

原帖由 yueliangdao0608 于 2008-3-1 09:11 发表



LZ没有说什么索引。

参见12楼

对了，主索引是work-id 和work-date组合