您好,我有一张下表,其中记录了活动和积分
Activites
|A_ID |Site | ActivityValue| ActivityName
---------------------------------------------------
|1 | site1 | 7 | ActivityName1
|2 | site1 | 6 | ActivityName2
|2 | site1 | 6 | ActivityName3
|2 | site1 | 6 | ActivityName4
pointsTable
|UserID |RemainderPoints | Active | ActivityName |
------------------------------------------------------
|1 | 10 | TRUE | ActivityName1|
|2 | 5 | TRUE | ActivityName2|
|2 | 5 | TRUE | ActivityName3|
|2 | 5 | TRUE | ActivityName3|
我需要的是返回活动的所有记录
SELECT ActivityName FROM activity WHERE `Site`='Site1'
Returns
ActivityName1
ActivityName2
ActivityName3
ActivityName4
然后我需要根据积分表中的用户 ID 求和用户积分
SELECT UserID, ActivityName, Active, SUM( RemainderPoints)
FROM pointstable
WHERE UserID =2
AND Active = 'TRUE' GROUP BY ActivityName
退货
|UserID |RemainderPoints | Active | ActivityName |
-------------------------------------------------------------
|2 | 5 | TRUE | ActivityName2|
|2 | 10 | TRUE | ActivityName3|
所有这些分开都很好,但我需要它们为用户组合在一起,即
ActivityName1 = 0
ActivityName2 = 5
ActivityName3 =10
ActivityName4 = 0
有什么指点吗?
使用外连接:
select
a.activityName,
coalesce(sum(pt.remainderPoints), 0) points
from
activities a left outer join pointsTable pt on
pt.activityName = a.activityName and pt.UserID = 2
group by a.activityName
或子查询:
select
activities.ActivityName,
coalesce(
(select sum(RemainderPoints) from pointsTable where pointsTable.UserId = 2 and pointsTable.ActivityName = activities.ActivityName)
, 0) points
from
activities
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)