--测试环境 Declare @t table(F1 varchar(10),F2 varchar(10),F3 int) insert into @t select 'A','BBC',12 union all select 'B','XXX',2 union all select 'A','DDD',1 union all select 'C','WWW',4 union all select 'B','GGG',5 --查询 select F1,F3=sum(F3) from ( select F1 As F1,F3 from @t union all select F2 As F1,F3 from @t ) A Group by F1 --结果 F1 F3 ---------- ----------- A 13 B 7 BBC 12 C 4 DDD 1 GGG 5 WWW 4 XXX 2
select F1 ,F3 from ( select F1 ,sum(F3) as 'F3' ,F1 as 'type' from A group by F1 union all select F2 ,F3 ,F1 from A )t order by type
select F1,sum(F3) AS F3 from 表 group by F1 union all select F2,F3 from 表
xueguang(xg) :你给的是什么啊,没看懂题吗?
declare @t table (F1 varchar(20),F2 varchar(20),F3 int)insert @t values ('A','BBC',12) insert @t values ('B','XXX',2) insert @t values ('A','DDD',1) insert @t values ('C','WWW',4) insert @t values ('B','GGG',5) select F1,sum(F3) AS F3 from @t group by F1 union all select F2,F3 from @tF1 F3 -------------------- ----------- A 13 B 7 C 4 BBC 12 XXX 2 DDD 1 WWW 4 GGG 5(所影响的行数为 8 行)
vivianfdlpw() :高手,只有你的是对的
你们看看谁的结果是下面的,一个也没有 F1 F2 A 13 BBC 12 DDD 1 B 7 XXX 2 GGG 5 C 4 WWW 4
--改一下vivianfdlpw() :的 select ID=identity(int,1,1),F1 ,F3 F2,type into # from ( select F1 ,sum(F3) as 'F3' ,F1 as 'type' from A group by F1 union all select F2 ,F3 ,F1 from A )tselect F1,F2 FROM # ORDER BY type,ID--结果 F1 F2 ---------- ----------- A 13 BBC 12 DDD 1 B 7 XXX 2 GGG 5 C 4 WWW 4(所影响的行数为 8 行)
create table A ( F1 varchar(10), F2 varchar(10), F3 int ) insert A select 'A', 'BBC', 12 union all select 'B', 'XXX', 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 XXX 2 GGG 5 C 4 WWW 4(8 row(s) affected) */
Declare @t table(F1 varchar(10),F2 varchar(10),F3 int)
insert into @t select 'A','BBC',12
union all select 'B','XXX',2
union all select 'A','DDD',1
union all select 'C','WWW',4
union all select 'B','GGG',5
--查询
select F1,F3=sum(F3)
from
(
select F1 As F1,F3 from @t
union all
select F2 As F1,F3 from @t
) A
Group by F1
--结果
F1 F3
---------- -----------
A 13
B 7
BBC 12
C 4
DDD 1
GGG 5
WWW 4
XXX 2
,F3
from
(
select F1
,sum(F3) as 'F3'
,F1 as 'type'
from A
group by F1 union all select F2
,F3
,F1
from A
)t
order by type
union all
select F2,F3 from 表
(F1 varchar(20),F2 varchar(20),F3 int)insert @t values ('A','BBC',12)
insert @t values ('B','XXX',2)
insert @t values ('A','DDD',1)
insert @t values ('C','WWW',4)
insert @t values ('B','GGG',5)
select F1,sum(F3) AS F3 from @t group by F1
union all
select F2,F3 from @tF1 F3
-------------------- -----------
A 13
B 7
C 4
BBC 12
XXX 2
DDD 1
WWW 4
GGG 5(所影响的行数为 8 行)
F1 F2
A 13
BBC 12
DDD 1
B 7
XXX 2
GGG 5
C 4
WWW 4
select ID=identity(int,1,1),F1
,F3 F2,type into #
from
(
select F1
,sum(F3) as 'F3'
,F1 as 'type'
from A
group by F1 union all select F2
,F3
,F1
from A
)tselect F1,F2 FROM # ORDER BY type,ID--结果
F1 F2
---------- -----------
A 13
BBC 12
DDD 1
B 7
XXX 2
GGG 5
C 4
WWW 4(所影响的行数为 8 行)
(
F1 varchar(10),
F2 varchar(10),
F3 int
)
insert A
select 'A', 'BBC', 12 union all
select 'B', 'XXX', 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
XXX 2
GGG 5
C 4
WWW 4(8 row(s) affected)
*/