请教一个SQL语句写法

dreameren 2010-10-20 05:01:16
表[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不分先后,其中可能用英文,中文]
...全文
57 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dreameren 2010-10-20
[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]
解决 谢谢 给分
回复
dreameren 2010-10-20
吃饭刚回来 先谢谢上边各位
回复
水族杰纶 2010-10-20
--或者是这样 猜吧
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
*/
回复
水族杰纶 2010-10-20
不知道楼主要开头的-结尾的
还是相连的

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

*/
回复
dawugui 2010-10-20
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 行)
*/
回复
dawugui 2010-10-20
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)
回复
水族杰纶 2010-10-20
2000
还是2005?
回复
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-20 05:01
社区公告
暂无公告