问题:表UserInfo(用户基本信息表),里面有:UserName,Password字段。表Account(用户财富表)里面有:UserName(与UserInfo.UserName关联),Type(财富类型:1金币,2游戏币,3财宝),Count(数量)这3个字段。怎样才能得到如下的查询结果:UserName,Password,金币,游戏币,财宝,数量。因为UserInfo表是一条记录,而Account是多条记录,而且还要将Account表的数据做为查询的列值。请大家帮帮忙。在线等....
调试欢乐多
sum(case when type = 2 then count else 0 end) as 游戏币,
sum(case when type = 3 then count else 0 end) as 财宝
from userinfo a
left join account b on a.username = b.username
group by a.username,a.password
,(SELECT SUM([A.Count]) FROM ACCOUNT A WHERE A.USERNAME=I.USERNAME AND A.[TYPE]=1) '金币'
,(SELECT SUM([A.Count]) FROM ACCOUNT A WHERE A.USERNAME=I.USERNAME AND A.[TYPE]=2) '游戏币'
,(SELECT SUM([A.Count]) FROM ACCOUNT A WHERE A.USERNAME=I.USERNAME AND A.[TYPE]=3) '财宝'
,(SELECT SUM([A.Count]) FROM ACCOUNT A WHERE A.USERNAME=I.USERNAME ) '总数量'
FROM USERINFO I
金币=sum(case type when 1 then COUNT else 0 end),
游戏币=sum(case type when 2 then COUNT else 0 end),
财宝=sum(case type when 3 then COUNT else 0 end),
数量=SUM([COUNT])
from userINFo U JOIN ACCOUNT A ON U.USERNAME=A.USERNAME
group by u.UserName,Password
select
a.username,a.password,
sum(case when type = 1 then count else 0 end)金币,
sum(case when type = 2 then count else 0 end)游戏币,
sum(case when type = 3 then count else 0 end)财宝
from
userinfo a
left join
account b
on
a.username = b.username
group by
a.username,a.password
金币=sum(case type when 1 then COUNT else 0 end),
游戏币=sum(case type when 2 then COUNT else 0 end),
财宝=sum(case type when 3 then COUNT else 0 end),
数量=isnull(SUM([COUNT]),0)
from userINFo U left JOIN ACCOUNT A ON U.USERNAME=A.USERNAME
group by u.UserName,Password
select
a.username,a.password,
sum(case when type = 1 then count else 0 end)金币,
sum(case when type = 2 then count else 0 end)游戏币,
sum(case when type = 3 then count else 0 end)财宝,
isnull(sum(count),0)数量
from
userinfo a
left join
account b
on
a.username = b.username
group by
a.username,a.password
,sum(case when B.[Type]=1 then B.[count] else 0 end) as '金币'
,sum(case when B.[Type]=2 then B.[count] else 0 end) as '游戏币'
,sum(case when B.[Type]=3 then B.[count] else 0 end) as '财宝'
,sum(B.[count]) as '总数'
From [UserInfo] A Inner Join [Account] B
On A.[username]=B.[username]
group by A.[username],A.[password]