GROUP BY 子句 指定用来放置输出行的组,并且如果 SELECT 子句 <select list> 中包含聚合函数,则计算每组的汇总值。指定 GROUP BY 时,选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中,或者 GROUP BY 表达式必须与选择列表表达式完全匹配。 说明 如果未指定 ORDER BY 子句,则使用 GROUP BY 子句不按任何特定的顺序返回组。建议始终使用 ORDER BY 子句指定具体的数据顺序。
SELECT OrdD.ProductID AS ProdID, SUM(OrdD.Quantity) AS AmountSold FROM [Order Details] AS OrdD JOIN Products as Prd ON OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2 GROUP BY OrdD.ProductID
简单: select min(col1)as col,sum(col1) as sum from tt group by col1
:) select col1,sum(col1) as sum from tt group by col1
SELECT OrdD.ProductID AS ProdID, SUM(OrdD.Quantity) AS AmountSold FROM [Order Details] AS OrdD JOIN Products as Prd ON OrdD.ProductID = Prd.ProductID AND Prd.CategoryID = 2 GROUP BY OrdD.ProductID with rollup 或 with cube
select node,weight from tree order by node compute SUM(weight) BY node 以NODE为字段 weight为相加
select node,weight from tree order by node compute SUM(weight) BY node 以NODE为字段 weight为相加
select node,weight from tree order by node compute SUM(weight) BY node 呵,应该能看出来吧
你要的结果: select * from ( select a,b from tablename union all select a,sum(b) as b from tablename group by a ) as tab order by a最好: select a,c,b from ( select a,' ' as c,b,1 as d from tablename union all select a,'小计' as c ,sum(b) as b,2 as d from tablename group by a ) as tab order by a,d 结果如下: 1 10 1 30 1 20 1 小计 60 2 30 2 小计 30 3 40 3 20 3 小计 60
select a,b,c from ( select a,b,c,1 as d,b as e from tablename union all select a,'小计' as b ,sum(c) as c,2 as d,min(b) as e from tablename group by a ) as tab order by e,d
指定用来放置输出行的组,并且如果 SELECT 子句 <select list> 中包含聚合函数,则计算每组的汇总值。指定 GROUP BY 时,选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中,或者 GROUP BY 表达式必须与选择列表表达式完全匹配。
说明 如果未指定 ORDER BY 子句,则使用 GROUP BY 子句不按任何特定的顺序返回组。建议始终使用 ORDER BY 子句指定具体的数据顺序。
SUM(OrdD.Quantity) AS AmountSold
FROM [Order Details] AS OrdD JOIN Products as Prd
ON OrdD.ProductID = Prd.ProductID
AND Prd.CategoryID = 2
GROUP BY OrdD.ProductID
select min(col1)as col,sum(col1) as sum from tt group by col1
select col1,sum(col1) as sum from tt group by col1
SUM(OrdD.Quantity) AS AmountSold
FROM [Order Details] AS OrdD JOIN Products as Prd
ON OrdD.ProductID = Prd.ProductID
AND Prd.CategoryID = 2
GROUP BY OrdD.ProductID
with rollup
或 with cube
以NODE为字段 weight为相加
以NODE为字段 weight为相加
呵,应该能看出来吧
id n
1 10
1 30
1 20
2 30
3 40
3 20
是想在网页上显示如下:
1 10
1 30
1 20
1 60
2 30
2 30
3 40
3 20
3 60
select * from (
select a,b from tablename
union all
select a,sum(b) as b from tablename group by a
) as tab
order by a最好:
select a,c,b from (
select a,' ' as c,b,1 as d from tablename
union all
select a,'小计' as c ,sum(b) as b,2 as d from tablename group by a
) as tab
order by a,d
结果如下:
1 10
1 30
1 20
1 小计 60
2 30
2 小计 30
3 40
3 20
3 小计 60
a b c
1 2 10
1 3 20
1 3 30
2 1 40
3 1 50
3 1 20
结果:
2 1 40
2小计1 40
3 1 50
3 1 20
3小计1 70
1 2 10
1小计2 10
1 3 20
1 3 30
1小计3 50
SELECT A,B,C
FROM table1
ORDER BY B,A
COMPUTE SUM(C) BY B,A
select a,b,c,1 as d,b as e from tablename
union all
select a,'小计' as b ,sum(c) as c,2 as d,min(b) as e from tablename group by a
) as tab
order by e,d