mysql摘要及group by技巧

 

 

1. 需要计算表记录行数的时候，对于MyISAM用count(*)，而对于BDB或InnoDB尽量使用information_schema中的table_rows中的值或者table status中的Rows的数值，以避免全表读取。

 

 

2. distinct用于选出唯一的记录。可修饰列名，以取出该列值唯一的记录。默认选出null值的记录。如果需要统计该列唯一值的个数，并包括null，可以使用：
count(distinct col_name) + if(sum(isnull(col_name))=0,0,1)
count(distinct col_name) + (sum(isnull(val)!=0)
计算记录重复率
select count(distinct)/count(*) as cover_rate from mtable;

 

 

3. 如果需要找出记录中某列的最大值或最小值的详细记录，必须使用子查询。
select * from mtable where col = (select max(col) from mtable);

 

 

4. 对于max或者min需要区分大小写。
select max(name collate latin1_general_cs) from mtable;

 

 


5. count,avg,min,max都不统计null值

 


6. 计算每个分组null值得个数。
select count(*) - count(col) from mtable group by col2;

 

7. 对于没有自然的数据集合，可以手工分组。
例如统计美国每个人口数量级的州的个数，以5000000为一个等级进行统计；
select floor((pop+4999999)/5000000)*5 as 'population millions',
count(*) as 'num of states'
from states group by 'population millions';
对于大小为n的类别可使用以下公式放入正确的组别中。
floor((x+(n-1)/n)

 

 


8. group by col默认按col顺序排列。如需要取消自动排列可以：
group by col order by null；


 

9. 按日期摘要
select dayname(col) from mtable group by dayofweek(col)

 

 

10. 同时需要总体摘要和分类摘要
select avg(col) from mtable group by col with roolup;

 

 

11. group by之后将某列的值按照摘要进行字符上的合并。即将A，B，C三个值合并成 'A,B,C'这样的一个值。

select name,group_concat(col1) from table_name group by name.