6,129
社区成员
发帖
与我相关
我的任务
分享
select --Mobile,a.UseWechatOpenId,UserName,
GroupName,isnull(a.ShareScore,0)+isnull(c.SecondShareScore,0) Integral,
isnull(a.ShareCount,0)ShareCount,isnull(a.ClickCount,0)ClickCount,isnull(a.JoinInUserCount,0)JoinInUserCount,isnull(b.ClickUserCount,0)ClickUserCount
from(
select a.TaskId,GroupName,
sum(case when OperationType=1 then 0 else 1 end) ShareCount,
sum(case when OperationType=1 then 1 else 0 end) ClickCount,SUM(ShareScore) ShareScore,
count( distinct ForwardWechatOpenId) JoinInUserCount
from tblTaskFlowRecord a left join tblTask b on a.TaskId=b.TaskId right join (
select a.TaskId,a.usergroupid,c.openid,e.GroupName from tblTaskRelationUserGroup a
left join tblUserGroupRelation b on a.UserGroupRelationId=b.UserGroupRelationId and b.IsDel=0
left join SysUser c on b.userid=c.userid
right join tblUserGroup e on e.UserGroupId = b.UserGroupId and e.IsDel = 0
)c on a.TaskId=c.TaskId and a.UseWechatOpenId=c.OpenId --where a.TaskId='CA83758E-9F6F-4376-88B1-E3B1703E011D'
group by a.TaskId,GroupName
) a left join (
select a.TaskId,count( distinct ForwardWechatOpenId) ClickUserCount
from tblTaskFlowRecord a where a.OperationType=1
group by a.TaskId
) b on a.TaskId=b.TaskId left join(
select TaskId,SUM(SecondShareScore) SecondShareScore
from tblTaskFlowRecord where OperationType=1
group by TaskId
) c on b.TaskId=c.TaskId
order by ShareCount desc