求各位前辈一个问题，关于汇总的问题
有一个类似这样的表格，
a 10000
b 1000
a 2000
b 300
c 40000
c 400
想得到的查询结果：
实现这样的目的：
ID SALEAMT 标识
a 10000 0
a 2000 0
a 12000 1
b 1000 0
b 300 0
b 1300 1
c 40000 0
c 400 0
c 40400 1
以惟一列ID为准，在每一个项目中，加一个汇总。其中的标识是为了排序用。
[解决办法]
select * from
(select ID,SALEAMT,0 as 标识 from YourTable
union all select id,sum(SALEAMT) as SALEAMT,1 as 标识 from YourTable group by id
) a
order by id,标识
[解决办法]
Create Table TEST
(ID Varchar(10),
SALEAMT Int)
Insert TEST Select 'a ', 10000
Union All Select 'b ', 1000
Union All Select 'a ', 2000
Union All Select 'b ', 300
Union All Select 'c ', 40000
Union All Select 'c ', 400
GO
--方法一
Select
ID,
SALEAMT,
0 As 标识
From
TEST
Union
Select
ID,
SUM(SALEAMT) As SALEAMT,
1 As 标识
From
TEST
Group By
ID
Order By
ID, 标识
--方法二
Select
ID,
SUM(SALEAMT) As SALEAMT
From
TEST
Group By
ID, SALEAMT
With Rollup
Having ID Is Not Null
GO
Drop Table TEST
--Result
/*
IDSALEAMT标识
a20000
a 100000
a 120001
b3000
b10000
b13001
c4000
c400000
c404001
IDSALEAMT
a2000
a 10000
a12000
b300
b1000
b1300
c400
c40000
c40400
*/
