sql server的group by语句

[ypwyy]

我想完成一个汇总语句，怕自己说不清楚，干脆举个例子：
月份 某人 数量
1 tt 12
1 yy 10
1 tt 13
2 zz 5
采用sum(数量）where 月份＝1 group by 某人得到的结果是
tt 25
yy 10
如果我想得到以下结果如何处理
tt 25
yy 10
zz 0

[liuyu202]

sum(数量）where 月份>0 group by 月份 group by 某人

[ypwyy]

好像不行
结果可能是
tt 25
yy 10

zz 5

[lawdoor]

11.2 用GROUP BY子句进行统计

GROUP BY 子句的语法格式为：

GROUP BY [ALL] aggregate_free_expression
[, aggregate_free_expression]

在SELECT 语句中，GROUP BY子句和集合函数对数据库进行分组统计，在查询结果中，每一组统计出一个结果。例如：

select fields as keystring,

COUNT(fields) as hitcount

from keys where
( keys like '%德' )

and

frm like '200*a'

GROUP BY fields
执行的结果为：
keystring hitcount
李祖德 1
桑关德 1
张京德 1
张冲德 1
（4 row(s) affected）
在上面的例子中，GROUP BY子句和集合函数COUNT按照fields字段对keys表进行分组统计。集合函数COUNT对每一组的行数进行计算，分组的标准是GROUP BY中定义的字段 fields。

[whyslr]

路过帮你顶一下。

[wanghr100]

有点烦.呵呵...

use Test
GO

CREATE View tmp1
AS
SELECT SUM(num) AS num, userid
FROM dbo.mun
WHERE ([month] = '1')
GROUP BY userid
GO

CREATE View tmp2
AS
SELECT userid
FROM dbo.mun
GROUP BY userid
GO

CREATE View tmpok
AS
SELECT dbo.t2.userid, ISNULL(dbo.t1.num, 0) AS Expr1
FROM dbo.t1 RIGHT OUTER JOIN
dbo.t2 ON dbo.t1.userid = dbo.t2.userid
GO

SELECT * FROM tmpok
GO

DROP VIEW tmp1
GO
DROP VIEW tmp2
GO
DROP VIEW tmpok
GO

[wanghr100]

对了,我的数据库是这样子的.
test
表mun
id,month,userid, num
1 1 tt 12
2 1 yy 10
3 1 tt 13
4 2 zz 5


use Test
GO

CREATE View tmp1
AS
SELECT SUM(num) AS num, userid
FROM dbo.mun
WHERE ([month] = '1')
GROUP BY userid
GO

CREATE View tmp2
AS
SELECT userid
FROM dbo.mun
GROUP BY userid
GO

SELECT dbo.tmp2.userid, ISNULL(dbo.tmp1.num, 0) AS Expr1
FROM dbo.tmp1 RIGHT OUTER JOIN
dbo.tmp2 ON dbo.tmp1.userid = dbo.tmp2.userid
GO

DROP VIEW tmp1
GO
DROP VIEW tmp2
GO

[skywatcher]

select distinct 某人,(select sum(数量) from yourtable:t2 where 月份=1) from yourtable:t1

试试看这个思路吧

[会织网的蚂蚁]

group by是区分重复的东西