显示一个表中的所有记录，并显示另一个表中的匹配记录

问题描述

我在下面的表格中记录了活动和要点

Hi I have a tables below that has records of activites and points

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|

我需要的是从活动"中返回所有记录

what I need is return all records from Activities

SELECT  ActivityName FROM activity WHERE `Site`='Site1'

返回

ActivityName1
ActivityName2
ActivityName3
ActivityName4

然后我需要根据点表中的用户ID对用户点求和

Then I need to sum user points based on user id from pointstable

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|

所有这些都可以单独使用，但我需要将它们放在一起给用户使用，即

All that's fine separately but I need them put together for the user i.e

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

这篇关于显示一个表中的所有记录，并显示另一个表中的匹配记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！