mysql - 多表多列查询

Question

我有以下表格:

交易 : Transaction_ID Datetime Giver_ID Recipient_ID Points Category_ID 原因

奖励 : Reward_ID Title Description Image_URL Date_Inactive Stock_Count Cost_to_User Cost_to_System

购买:Purchase_ID Datetime Reward_ID Quantity Student_ID Student_Name Date_DealtWith Date_Collected

---

工作人员用积分奖励学生，积分成为交易表的条目，如下所示:

Transaction_ID Datetime Giver_ID Recipient_ID Points Category_ID 原因
8 2011-09-07 36761 127963 2 1 行为单元优秀行为

然后学生可以购买一个奖励，它成为购买表的条目，如下所示:

Purchase_ID 日期时间 Reward_ID 数量 Student_ID Student_Name Date_DealtWith Date_Collected
1570 2012-05-30 12:46:36 2 1 137616 布拉德利理查森 NULL NULL

所有奖励都手动存储在奖励数据库表中:

Reward_ID 标题 描述 Image_URL Date_Inactive Stock_Count Cost_to_User Cost_to_System
1 午餐时间排队通行证(月) 打败队列，早点吃热食! /user/74/167976。 png 2012-04-16 11:50:00 0 100 0

---

我的问题是:

我可以使用什么 SQL 语句来返回学生姓名、获得的分数、花费的分数、剩余的分数？

我问了a similar question a while back ，它提供了以下声明。但是，经过检查，它似乎并不完全准确 - 特别是 Points Spent 无法正常工作。

SELECT  Recipient_ID AS StudentID,
        SumOfPointsOfPurchasesMade.Points AS PurchasesMade,
        SumOfPointsEarned.Points AS PointsEarned,
        SumOfPointsEarned.Points - COALESCE(SumOfPointsOfPurchasesMade.Points, 0) AS CurrentPoints
FROM   
(
    SELECT SUM(Points) AS Points, Recipient_ID 
    FROM   transactions
    GROUP  BY Recipient_ID
) AS SumOfPointsEarned 
    LEFT JOIN 
    (
        SELECT purchases.Student_ID,  SUM(rewards.Cost_to_User) AS Points 
        FROM   purchases 
            INNER JOIN rewards 
            ON purchases.Reward_ID = rewards.Reward_ID
        GROUP  BY purchases.Student_ID
    ) AS SumOfPointsOfPurchasesMade 
    ON SumOfPointsEarned.Recipient_ID = SumOfPointsOfPurchasesMade.Student_ID
WHERE SumOfPointsEarned.Points < SumOfPointsOfPurchasesMade.Points
ORDER BY `CurrentPoints`  DESC

提前致谢

Answer 1

你只需要使用 outer join合并表格，然后按学生分组:

SELECT purchases.Student_Name                               AS `Student Name`,
       SUM(transactions.Points)                             AS `Points Earned`,
       SUM(rewards.Cost_to_User)                            AS `Points Spent`,
       SUM(transactions.Points) - SUM(rewards.Cost_to_User) AS `Points Remaining`
FROM   transactions
  LEFT JOIN purchases ON purchases.Student_ID = transactions.Recipient_ID
  LEFT JOIN rewards   USING (Reward_ID)
GROUP BY purchases.Student_ID
ORDER BY `Points Remaining` DESC