mysql groupby 怎么用

例如一个成绩表，包括学生id，科目，和各科分数score，要统计所有的人的总成绩，可以使用以下语句：

select id, sum(score) from 成绩表

group by id

易客CRM之前的版本中有一个报表是按月统计销售情况，最近有个客户想按周统计销售情况。按月统计的Sql语句比较好写，sql语句如下：

SELECT DATE_FORMAT(ec_salesorder.duedate,’%Y-%m’) as m, sum(ec_salesorder.total) as total, count(*) as so_count FROM ec_salesorder GROUP BY m ORDER BY m，也就是把duedate日期以月的形式显示，然后groupby，那么按周如何统计呢？

搜了一下mysql的manual，在这里找到一个解决方法，通过mysql的week函数来做，sql语句如下：SELECT WEEK(ec_salesorder.duedate) as m, sum(ec_salesorder.total) as total, count(*) as so_count FROM ec_salesorder GROUP BY m ORDER BY m，这个方法有个缺陷，不能显示年份，仅仅靠一个周数不方便查看统计信息。

继续研究mysql manual，在DATE_FORMAT函数介绍发现2个格式符和周有点关系：

%X Year for the week where Sunday is the first day of the week, numeric, four digitsused with %V

%x Year for the week, where Monday is the first day of the week, numeric, four digitsused with %v

把上面的Sql语句中改成：

SELECT DATE_FORMAT(ec_salesorder.duedate,’%x %v’) as m, sum(ec_salesorder.total) as total, count(*) as so_count FROM ec_salesorder GROUP BY m ORDER BY m

显示的结果如下：

m total so_count

2009 11 10000.00 3

2009 12 44000.00 5

如果周日为一周的第一天，那么sql语句应该为:

SELECT DATE_FORMAT(ec_salesorder.duedate,’%X %V’) as m, sum(ec_salesorder.total) as total, count(*) as so_count FROM ec_salesorder GROUP BY m ORDER BY m

结果应该没错，不出意外，易客CRM下个版本将增加按周统计销售情况的报表。

tab_game

mysql>select * from tab_game

+----+-----------+-----------+-----------------+

| id | game_code | game_name | game_desc |

+----+-----------+-----------+-----------------+

| 1 | 1001 | doudizhu | doudizhuhaowan |

| 2 | 1002 | majiang | cuomajiangbucuo |

| 3 | 1003 | fahongbao | hongbaoxiangyao |

+----+-----------+-----------+-----------------+

tab_game_version

mysql>select * from tab_game_version

+----+-----------+------------------+-----------+

| id | game_code | game_version_int | game_size |

+----+-----------+------------------+-----------+

| 1 | 1001 | 11 | 5MB |

| 2 | 1001 | 12 | 6MB |

| 3 | 1001 | 15 | 8MB |

| 4 | 1002 | 10 | 1MB |

| 5 | 1002 | 20 | 2MB |

| 6 | 1003 | 1000 | 7MB |

| 7 | 1003 | 2000 | 9MB |

+----+-----------+------------------+-----------+

有这样的两张表，要查出每个游戏的最大的game_version_int值的记录

最终想得到的结果是

game_code game_name game_version_int game_size

1001斗地主 15 8MB

1002搓麻将 20 2MB

1003发送包 2000 9MB

SQL 实现

第一种方式 不通用，只在MYSQL中可以用

mysql> select game_code,max(game_version_int),game_size

->select * from tab_game_version t1 order by t1.gam

nt desc) t group by game_code

+-----------+-----------------------+-----------+

| game_code | max(game_version_int) | game_size |

+-----------+-----------------------+-----------+

| 1001 |15 | 8MB |

| 1002 |20 | 2MB |

| 1003 | 2000 | 9MB |

+-----------+-----------------------+-----------+

第二种方式，采用字符串拼接的方式，可以解决

mysql> select * from tab_game_version where

->concat(game_code,game_version_int)

->in (select concat(game_code,max(game_version_int)) from tab_game_version

t1 group by t1.game_code )

+----+-----------+------------------+-----------+

| id | game_code | game_version_int | game_size |

+----+-----------+------------------+-----------+

| 3 | 1001 | 15 | 8MB |

| 5 | 1002 | 20 | 2MB |

| 7 | 1003 | 2000 | 9MB |

+----+-----------+------------------+-----------+

第三种方式,

mysql> select a.game_code,b.game_size,a.game_version_int,c.game_name from (sele

ct game_code,max(game_version_int) game_version_int from tab_game_version group

by game_code) a

->left join tab_game_version b on a.game_code = b.game_code and a.game_vers

ion_int = b.game_version_int left join tab_game c on b.game_code = c.game_code

+-----------+-----------+------------------+-----------+

| game_code | game_size | game_version_int | game_name |

+-----------+-----------+------------------+-----------+

| 1001 | 8MB | 15 | doudizhu |

| 1002 | 2MB | 20 | majiang |

| 1003 | 9MB | 2000 | fahongbao |

+-----------+-----------+------------------+-----------+

---