请教一个SQL语句写法

[dreameren]

表[test]
字段
account1,account2,type,num
123 , 789 ,5 ,3
789 , 123 ,5 ,5
abc , xxx ,6 ,10
xxx , abc ,6 ,12
yyy , zzz ,7 ,8


最后统计成这种形式：
123 , 789 ,5 ,8
abc , xxx ,6 ,22
yyy , zzz ,7 ,8
[account1,account2不分先后，其中可能用英文，中文]

[水族杰纶]

2000
还是2005？

[dawugui]

select (case when account1 < account2 then account1 else account2 end) account1,
       (case when account1 < account2 then account2 else account1 end) account2,、     
       max(type) type,
       sum(num)
group by (case when account1 < account2 then account1 else account2 end) ,
       (case when account1 < account2 then account2 else account1 end)

[dawugui]

create table tb(account1 varchar(10),account2 varchar(10),type int,num int)
insert into tb values('123' , '789' ,5 ,3)
insert into tb values('789' , '123' ,5 ,5)
insert into tb values('abc' , 'xxx' ,6 ,10)
insert into tb values('xxx' , 'abc' ,6 ,12)
insert into tb values('yyy' , 'zzz' ,7 ,8)
go

select (case when account1 < account2 then account1 else account2 end) account1,
       (case when account1 < account2 then account2 else account1 end) account2,     
       max(type) type, 
       sum(num) num
from tb
group by (case when account1 < account2 then account1 else account2 end) ,
       (case when account1 < account2 then account2 else account1 end)

drop table tb

/*
account1   account2   type        num         
---------- ---------- ----------- ----------- 
123        789        5           8
abc        xxx        6           22
yyy        zzz        7           8

（所影响的行数为 3 行）
*/

[水族杰纶]

不知道楼主要开头的-结尾的
还是相连的

if not object_id('tb') is null
	drop table tb
Go
Create table tb([account1] nvarchar(3),[account2] nvarchar(3),[type] int,[num] int)
Insert tb
select N'123',N'789',5,3 union all
select N'789',N'123',5,5 union all
select N'abc',N'xxx',6,10 union all
select N'xxx',N'abc',6,12 union all
select N'yyy',N'zzz',7,8
Go
;with tt
as(
select [account1],
       [type],
       sum(num)num
from(
select [account1] ,[type],[num] from tb
union all
select [account2] ,[type],[num] from tb
)t
group by [account1],[type])
select account=stuff((select ','+account1
                      from tt
                      where [type]=t.[type]
                      for xml path('')),1,1,''),
       [type],
       num 
from tt t
group by [type],num
/*
account                                                                                                                                                                                                                                                          type        num
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
123,789                                                                                                                                                                                                                                                          5           8
abc,xxx                                                                                                                                                                                                                                                          6           22
yyy,zzz                                                                                                                                                                                                                                                          7           8

*/

[水族杰纶]

--或者是这样 猜吧
if not object_id('tb') is null
	drop table tb
Go
Create table tb([account1] nvarchar(3),[account2] nvarchar(3),[type] int,[num] int)
Insert tb
select N'123',N'789',5,3 union all
select N'789',N'123',5,5 union all
select N'abc',N'xxx',6,10 union all
select N'xxx',N'abc',6,12 union all
select N'yyy',N'zzz',7,8
Go

select min([account1])[account1],
       max([account1])[account2],
       [type],
       sum(num)num
from(
select [account1] ,[type],[num] from tb
union all
select [account2] ,[type],[num] from tb
)t
group by [type]
/*
account1 account2 type        num
-------- -------- ----------- -----------
123      789      5           16
abc      xxx      6           44
yyy      zzz      7           16
*/

[dreameren]

吃饭刚回来 先谢谢上边各位

[dreameren]

[Quote=引用 3 楼 dawugui 的回复:]

SQL code
create table tb(account1 varchar(10),account2 varchar(10),type int,num int)
insert into tb values('123' , '789' ,5 ,3)
insert into tb values('789' , '123' ,5 ,5)
insert into tb values('abc'……
[/Quote]
解决 谢谢 给分