新手的问题:假如有如下形式的表记录了员工的ID号,日期,生产率(索引是 work-id+prod_dat组合
+----------+------------+---------------+
| work-id | prod_dat | prod_num) |
+----------+------------+---------------+
| 3396 | 2008-01-02 | 128 |
| 3396 | 2008-01-03 | 123 |
| 3396 | 2008-01-04 | 132|
| 3397 | 2008-01-02 | 56 |
| 3397 | 2008-01-03 | 43 |
| 3397 | 2008-01-04 | 77 |
| 3397 | 2008-01-05 | 62 |
那么可以方便的找出员工最大和最小生产率,如select work-id,max(prod_dat),min(prod_num)
from table_name
group by 1但是如果我想同时找出对于每个员工最大及最小生产率所对应的日期,该怎么写这个查询?试了半天都失败,不知如何下手。试过加入子查询,但是结果总不正确。mysql的手册还是较为简略,主要是语法,没有提供相关的SQL查询技巧。请费心指教,谢谢!另外,有关SQL的查询技巧,哪一本书较为全面,适合我这样入门新手级别学习的?可以的话,烦请一并推荐!
+----------+------------+---------------+
| work-id | prod_dat | prod_num) |
+----------+------------+---------------+
| 3396 | 2008-01-02 | 128 |
| 3396 | 2008-01-03 | 123 |
| 3396 | 2008-01-04 | 132|
| 3397 | 2008-01-02 | 56 |
| 3397 | 2008-01-03 | 43 |
| 3397 | 2008-01-04 | 77 |
| 3397 | 2008-01-05 | 62 |
那么可以方便的找出员工最大和最小生产率,如select work-id,max(prod_dat),min(prod_num)
from table_name
group by 1但是如果我想同时找出对于每个员工最大及最小生产率所对应的日期,该怎么写这个查询?试了半天都失败,不知如何下手。试过加入子查询,但是结果总不正确。mysql的手册还是较为简略,主要是语法,没有提供相关的SQL查询技巧。请费心指教,谢谢!另外,有关SQL的查询技巧,哪一本书较为全面,适合我这样入门新手级别学习的?可以的话,烦请一并推荐!
from (table_name t inner join
(select work-id,max(prod_num) as max_prod_num from table_name) ma
on t.work-id=ma.work-id and t.prod_num=ma.max_prod_num)
inner join
(select work-id,min(prod_num) as min_prod_num from table_name) mi
on t.work-id=mi.work-id and t.prod_num=mi.min_prod_num
或参考:
http://www.csdn.net/help/over.asp
http://topic.csdn.net/u/20080110/19/7cb462f1-cac6-4c28-848e-0a879f4fd642.html
=============================================================================
问题解决,请及时结贴。
正确结贴方法:
管理帖子-->给分-->输入密码-->结贴
(select work-id,max(prod_num) as ma,min(prod_num) as mi
from table_name
group by 1 ) b
inner join tt a on a.work-id=b.work-id and b.ma=a.prod_num
inner join tt c on b.work-id=c.work-id and b.mi=c.prod_num