create table tb
(
F1 varchar(10),
F2 varchar(10),
F3 int
)
insert tb
select 'A', 'BBC', 12 union
select 'B', 'XXX', 2 union
select 'A', 'DDD', 1 union
select 'C', 'WWW', 4 union
select 'B', 'GGG', 5
go
select f1,f2 from (
select f=f1,f1,f2=sum(f3)
from tb group by f1
union all
select f1,f2,sum(f3)
from tb group by f1,f2
) aa order by f
/*
f1 f2
---------- -----------
A 13
BBC 12
DDD 1
B 7
GGG 5
XXX 2
C 4
WWW 4(所影响的行数为 8 行)
*/
drop table tb
(
F1 varchar(10),
F2 varchar(10),
F3 int
)
insert tb
select 'A', 'BBC', 12 union
select 'B', 'XXX', 2 union
select 'A', 'DDD', 1 union
select 'C', 'WWW', 4 union
select 'B', 'GGG', 5
go
select f1,f2 from (
select f=f1,f1,f2=sum(f3)
from tb group by f1
union all
select f1,f2,sum(f3)
from tb group by f1,f2
) aa order by f
/*
f1 f2
---------- -----------
A 13
BBC 12
DDD 1
B 7
GGG 5
XXX 2
C 4
WWW 4(所影响的行数为 8 行)
*/
drop table tb
insert into A select 'A','BBC',12
insert into A select 'B','AAA',2
insert into A select 'A','DDD',1
insert into A select 'C','WWW',4
insert into A select 'B','GGG',5select identity(int,1,1) as id,* into #T from Aselect
F11=isnull(b.F2,b.F1),F21=b.F3
from
(select 0 as id,F1,F2=NULL,F3=sum(F3) from #t group by F1
union all
select id,F1,F2,F3=sum(F3) from #T group by id,F1,F2) b
order by b.F1,b.id/*
F11 F21
A 13
BBC 12
DDD 1
B 7
AAA 2
GGG 5
C 4
WWW 4
*/drop table A,#t
(
F1 varchar(10),
F2 varchar(10),
F3 int
)
insert A
select 'A', 'BBC', 12 union all
select 'B', 'AAA', 2 union all
select 'A', 'DDD', 1 union all
select 'C', 'WWW', 4 union all
select 'B', 'GGG', 5--查询
select identity(int,1,1) id,* into # from Aselect F1
,F3
from
(
select F1
,sum(F3) as 'F3'
,F1 as 'type'
,0 as 'id'
from #
group by F1 union all select F2
,F3
,F1
,id
from #
)t
order by type,id--删除测试环境
drop table #
drop table A--结果
/*F1 F3
---------- -----------
A 13
BBC 12
DDD 1
B 7
AAA 2
GGG 5
C 4
WWW 4(8 row(s) affected)
*/
(
F1 varchar(10),
F2 varchar(10),
F3 int
)
insert tb
SELECT '话别','BBC',12 UNION ALL
SELECT '以便','XXX',2 UNION ALL
SELECT '话别','DDD',1 UNION ALL
SELECT '人才','WWW',4 UNION ALL
SELECT '以便','GGG',5goselect f1,f2 from (
select f=f1,f1,f2=sum(f3)
from tb group by f1
union all
select f1,f2,sum(f3)
from tb group by f1,f2
) aa order by f,f1 desc
/*
f1 f2
---------- -----------
话别 13
DDD 1
BBC 12
人才 4
WWW 4
以便 7
XXX 2
GGG 5(所影响的行数为 8 行)
*/
drop table tb
create table tb
(
F1 varchar(10),
F2 varchar(10),
F3 int
)
insert tb
SELECT '话别','BBC',12 UNION ALL
SELECT '以便','XXX',2 UNION ALL
SELECT '话别','DDD',1 UNION ALL
SELECT '人才','WWW',4 UNION ALL
SELECT '以便','GGG',5goselect f1,f2 from (
select f=f1,f1,f2=sum(f3),0 as 'flag'
from tb group by f1
union all
select f1,f2,sum(f3),1 as 'flag'
from tb group by f1,f2
) aa order by f,flag,f1
/*
f1 f2
---------- -----------
话别 13
DDD 1
BBC 12
人才 4
WWW 4
以便 7
XXX 2
GGG 5(所影响的行数为 8 行)
*/
drop table tb
有一表A
F1 F2 F3
话别 BBC 12
以便 XXX 2
话别 DDD 1
人才 WWW 4
以便 GGG 5
现要得到 注意结果
F1 F2
话别 13
BBC 12
DDD 1
以便 7
XXX 2
GGG 5
人才 4
WWW 4
那么可能要复杂一些吧!
Declare @t table(F1 varchar(10),F2 varchar(10),F3 int)
insert into @t values ('话别','BBC',12)
insert into @t values ( '以便','XXX',2)
insert into @t values ('话别','DDD',1)
insert into @t values ('人才','WWW',4)
insert into @t values ('以便','GGG',5)select identity(int,1,1) as id,* into #T from @tselect id=(select count(1) from (select min(id) as id,f1,sum(f3) as f3 from #t group by f1) a
where a.id<=b.id),f1,f3 into #
from
(
select min(id) as id,f1,sum(f3) as f3 from #t group by f1
) bselect f1,f3 from
(
select cast(id as varchar) as id,f1,f3 from #
union all
select cast(a.id as varchar)+cast(b.id as varchar) as id,b.f2,b.f3 from # a inner join #t b on a.f1=b.f1
) m order by id
drop table #t
drop table #(所影响的行数为 3 行)f1 f3
---------- -----------
话别 13
BBC 12
DDD 1
以便 7
XXX 2
GGG 5
人才 4
WWW 4(所影响的行数为 8 行)