SQL group by的困惑

今天为写一条SQL困扰了很久

首先先贴两条sql，大家看看有啥区别

[sql] view plain copy print ?

1. select * from all_txt Awhere id=(SELECT idfrom all_txt   

[sql] view plain copy print ?

1. select * from all_txt A where id=(SELECT id from all_txt   

select * from all_txt A where id=(SELECT id from all_txt  

[sql] view plain copy print ?

1. where A.key_value = key_valueORDER BY release_timeDESC limit 1); 

[sql] view plain copy print ?

1. where A.key_value = key_value ORDER BY release_time DESC limit 1); 

where A.key_value = key_value ORDER BY release_time DESC limit 1);

[sql] view plain copy print ?

1. select * from (select *,max(release_time)as timea  

[sql] view plain copy print ?

1. select * from (select *,max(release_time) as timea  

select * from (select *,max(release_time) as timea 

[sql] view plain copy print ?

1. from all_txt groupby key_value orderby release_time desc) aorder by timeadesc; 

[sql] view plain copy print ?

1. from all_txt group by key_value order by release_time desc) a order by timea desc; 

from all_txt group by key_value order by release_time desc) a order by timea desc;

然后说明下表的重要字段为三个id,key_value,release_time
作用是取每个相同key_value中release_time最新的数据

所有的数据为

[plain] view plain copy print ?

1. id  key_value release_time 
2. 1    001    2007-11-01 
3. 2    001    2007-11-02 
4. 3    001    2007-11-03 
5. 4    002    2007-11-04 
6. 5    002    2007-11-05 
7. 6    003    2007-11-06 
8. 7    004    2007-11-07 
9. 8    004    2007-11-08 
10. 9    005    2007-11-09 
11. 10   005    2007-11-10 

[plain] view plain copy print ?

1. id  key_value release_time 
2. 1    001    2007-11-01 
3. 2    001    2007-11-02 
4. 3    001    2007-11-03 
5. 4    002    2007-11-04 
6. 5    002    2007-11-05 
7. 6    003    2007-11-06 
8. 7    004    2007-11-07 
9. 8    004    2007-11-08 
10. 9    005    2007-11-09 
11. 10   005    2007-11-10 

id  key_value release_time
1    001    2007-11-01
2    001    2007-11-02
3    001    2007-11-03
4    002    2007-11-04
5    002    2007-11-05
6    003    2007-11-06
7    004    2007-11-07
8    004    2007-11-08
9    005    2007-11-09
10   005    2007-11-10

现在要的结果为

[plain] view plain copy print ?

1. id  key_value release_time 
2. 3    001    2007-11-03 
3. 5    002    2007-11-05 
4. 6    003    2007-11-06 
5. 8    004    2007-11-08 
6. 10   005    2007-11-10 

[plain] view plain copy print ?

1. id  key_value release_time 
2. 3    001    2007-11-03 
3. 5    002    2007-11-05 
4. 6    003    2007-11-06 
5. 8    004    2007-11-08 
6. 10   005    2007-11-10 

id  key_value release_time
3    001    2007-11-03
5    002    2007-11-05
6    003    2007-11-06
8    004    2007-11-08
10   005    2007-11-10

针对这些数据，显示貌似都一样
但仔细研究下来就会发现
第二句 返回的会是

[plain] view plain copy print ?

1. id  key_value timea     release_time 
2. 3    001    2007-11-03  2007-11-01 
3. 5    002    2007-11-05  2007-11-04 
4. 6    003    2007-11-06  2007-11-06 
5. 8    004    2007-11-08  2007-11-07 
6. 10   005    2007-11-10  2007-11-09 

[plain] view plain copy print ?

1. id  key_value timea     release_time 
2. 3    001    2007-11-03  2007-11-01 
3. 5    002    2007-11-05  2007-11-04 
4. 6    003    2007-11-06  2007-11-06 
5. 8    004    2007-11-08  2007-11-07 
6. 10   005    2007-11-10  2007-11-09 

id  key_value timea     release_time
3    001    2007-11-03  2007-11-01
5    002    2007-11-05  2007-11-04
6    003    2007-11-06  2007-11-06
8    004    2007-11-08  2007-11-07
10   005    2007-11-10  2007-11-09

用max求出来的数据timea是没错，是上面的release_time，但真实的release_time却是不一样的，那证明取的数据不是最新的那条的，而是给我感觉取的是物理位置上的第一条，这样的话如果你还需要这张表的其他字段就会有问题。
说了半天有人会说，那你就用第一种吧，执行下你就会发现，性能太差了，而第二种就很快，本以为能用第二种的，哎~~~~可能是对group by的本质还是了解不是很透彻
大家有啥更好的优化意见或方法，可以给我留言或加入qq群:47833489 讨论