sql group by语法与实例

group by 语句

group by 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

sql group by 语法

select column_name, aggregate_function(column_name)

from table_name

where column_name operator value

group by column_name

来看一个group by实例

比如表里内容是这样

gameid best top spam

111 1 1 1

121 1 1 0

123 0 1 1

111 1 0 1

我现在要取出best top spam 的数量 结果应该是

gameid best top spam

111 2 1 2

121 1 1 0

123 0 1 1

select gameid, sum(best) as bestcnt, sum(top) as tocnt, sum(spam) as spamcnt

from table

group by gameid

在php教程 中使用方法

$sql = "select gameid,sum(best) as best,sum(top) as top,sum(spam) as spam, from tablename group by gameid order by gameid asc";

再来看个简单一点的实例

数据库教程表table

日期 胜负

2009-12-9 胜

2009-12-9 胜

2009-12-9 负

2009-12-9 负

2009-12-10 负

2009-12-10 胜

2009-12-10 负

查询结果

日期 胜 负

2009-12-9 2 2

2009-12-10 1 2

select 日期,

sum(decode(胜负,'胜',1,0)),

sum(decode(胜负,'负',1,0))

from 数据库表

group by 日期