这个问题如何解决,高手进

[wzj1321]

如表 T_1 中有字段 A,B 假设数据如下
A B
王 12
李 10
王 20
张 30
我想返回 1 王 12
2 王 20
3 王(小计) 32
4 李 10
5 李(小计) 10
6 张 30
7 张(小计) 30 用 union all 来实现可以吗??? 不能用存储过程,也不能新建表,只能用select来返回,

[xiaxia421]

楼上的强悍

[paoluo]

Select
ID = Identity(Int, 1, 1),
(Case When Grouping(B) = 1 Then A + N'(小计)' Else A End) As A,
SUM(B) As B
Into #T
From
T_1
Group By A, B
With Rollup
Having A Is Not Null

Select * From #T

[paoluo]

--用With Rollup的方法

--創建測試環境
Create Table T_1
(
A NvarChar(10),
B Int)

Insert Into T_1 Select N'王',12
Union Select N'李',10
Union Select N'王',20
Union Select N'张',30
GO
--測試
Select
ID = Identity(Int, 1, 1),
(Case When Grouping(B) = 1 Then A + N'(小计)' Else A End) As A,
SUM(B) As B
Into #T
From
T_1
Group By A, B
With Rollup
Having A Is Not Null

Select * From #T

Drop Table #T
GO
--刪除測試環境
Drop Table T_1
--結果
/*
ID A B
1 王 12
2 王 20
3 王(小计) 32
4 李 10
5 李(小计) 10
6 张 30
7 张(小计) 30
*/

[paoluo]

--用Union的方法

--創建測試環境
Create Table T_1
(
A NvarChar(10),
B Int)

Insert Into T_1 Select N'王',12
Union Select N'李',10
Union Select N'王',20
Union Select N'张',30
GO
--測試
Select
ID = Identity(Int, 1, 1), *
Into #T
From
(Select * From T_1
Union
Select A + N'(小计)', SUM(B) As B From T_1 Group By A) A

Select * From #T

Drop Table #T
GO
--刪除測試環境
Drop Table T_1
--結果
/*
ID A B
1 王 12
2 王 20
3 王(小计) 32
4 李 10
5 李(小计) 10
6 张 30
7 张(小计) 30
*/

[yesterday2000]

create table cs (a char (10),b int )
insert into cs
select '王', 12
union all
select '李', 10
union all
select '王', 20
union all
select '张', 30

select a,b
from cs
union all
select a+'(小计)',sum(b) as b
from cs
group by a
order by a

[jacket008]

select * from t_1
union all
select A+'(小计)',sum(B) from t_1
group by A+'(小计)'
order by A

[dawugui]

select a,sum(b) b from t_1 COMPUTE SUM(b)

[wzj1321]

请看清楚要求啊,要求行数,就是要返回前面的1,2,3,4,5,6,7

[jacket008]

declare @tb1 table(id int identity(1,1),A varchar(20),B int)

insert into @tb1 (A,B)
select * from t_1
union all
select A+'(小计)',sum(B) from t_1
group by A+'(小计)'
order by A

select * from @tb1

[OracleRoob]

如果增加你要求的序号，需要借助临时表

[power6d]

Create Table #Tmp1
(
A Char(2),
B Integer
)

Insert Into #Tmp1
Select '王',12 Union
Select '李',10 Union
Select '王',20 Union
Select '张',30

Select * From #Tmp1

Select Shtno=(Select Count(*)+1 From (Select * From #Tmp1 Union All Select A+'(小计)' As A,Sum(B) As B From #Tmp1 Group By A) D Where
D.A+Cast(D.B As Char(10))<C.A+Cast(C.B As Char(10))),*
From (Select * From #Tmp1 Union All Select A+'(小计)' As A,Sum(B) As B From #Tmp1 Group By A) C Order By Shtno