问题2
declare @TB_Union table(UnionID int,UnionName varchar(20))
insert @TB_Union values(1,'黄小明')
insert @TB_Union values(2,'李小强')
insert @TB_Union values(3,'张小天')
declare @TB_User table(UserID int,UserMoney int,UserType varchar(20),UserUnionID int,UserHandUp varchar(20))
insert @TB_User values(1,7,'移动',1,'20060107')
insert @TB_User values(2,10,'联通',1,'20060107')
insert @TB_User values(3,7,'移动',1,'20060107')
insert @TB_User values(4,7,'移动',2,'20060107')
insert @TB_User values(5,7,'移动',1,'20060107')
insert @TB_User values(6,7,'移动',1,'20060106')
/*
select [TjmMobile]=isnull((case when UserType='移动' then count(UserMoney) end ),0)
,[TjmUnicom]=isnull((case when UserType='联通' then count(UserMoney) end),0),
[TjmMoney]=sum(UserMoney)
from @TB_User group by UserType,UserUnionID
*/
select a.UnionID,a.UnionName,[UserHandUp]=isnull(max(b.UserHandUp),0),
[TjmMobile]=isnull(sum(b.TjmMobile),0),
[TjmUnicom]=isnull(sum(b.TjmUnicom),0),
[TjmMoney]=isnull(sum(b.TjmMoney),0)
from @TB_Union a left join
(
select [TjmMobile]=isnull((case when UserType='移动' then count(UserMoney) end ),0)
,[TjmUnicom]=isnull((case when UserType='联通' then count(UserMoney) end),0),
[TjmMoney]=sum(UserMoney),
UserHandUp,UserUnionID
from @TB_User group by UserType,UserUnionID,UserHandUp
)b on a.UnionID=b.UserUnionID
group by a.UnionID,a.UnionName
---结果
/*
UnionID UnionName UserHandUp TjmMobile TjmUnicom TjmMoney
----------- -------------------- -------------------- ----------- ----------- -----------
1 黄小明 20060107 4 1 38
2 李小强 20060107 1 0 7
3 张小天
*/