mysql - 使用嵌套连接从三个表中选择列

Question

我创建了下表:

用户表

user_id (primary key)
account_created (date)
email (varchar)
usage_count (number)

产品表

product_id (primary key)
product (varchar) (values include “iPhone”, “Android”, “Windows”)
users_supported (number) 

(支持用户说明:部分话机最多支持1000人群组通话，部分话机仅支持2人普通通话)

使用表

usage_id (primary key)
product_id (foreign key)
user_id (foreign key)
usage_date (date)
purchase_call (number) (can be a 0, 2, 4, 6, or 10 min call)
usage_winnings (number) (when users use their minutes, sometimes they will randomly earn cash back)
computer_usage (binary value) (users can link the phone to a computer, and make calls through their computer, similar to google voice)

付款表

payment_id (primary key)
user_id (foreign key)
payment_type (char) (either D or W)
 (D to deposit money to purchase minutes, or W to withdraw cash back bonus from usage_winnings)
payment_date (date)
amount (number)

我想编写一个显示以下列表的选择语句:

1. 2016 年为 iPhone 购买的总分钟数
2. 2016 年为其他类型手机购买的总分钟数
3. 用户从 use_winnings 中收到的总金额
4. 2016 年所有手机通过计算机调用的电话百分比(为了进一步解释这一点，您可以为 iPhone 购买通话时间，然后通过计算机使用该 iPhone 调用电话，类似于 Google Voice)
5. 最后一次付费使用电话的日期(0 分钟的电话或短于一分钟的电话被视为免费，因此仅包含 2、4、6 或 10 的值)
6. 用户帐户中的净帐户余额(存款 - 取款)

具有以下限制:至少使用过一次(他们的帐户中有一些分钟)，在 2016 年创建了帐户，并在 2013 年使用过 iPhone。

到目前为止我已经:

SELECT u.user_id, SUM(us.purchase_minutes), SUM(us.purchase_minutes), COUNT(us.usage_winnings), COUNT(us.computer_usage) / SELECT COUNT(us.usage_id), us.usage_date, pa.amount
FROM ‘USER’ u, ‘USAGE’ us, ‘PAYMENTS’ pa
INNER JOIN USAGE us ON u.user_id = us.user_id
JOIN PRODUCT p ON us.product_id = p.product_id
JOIN PAYMENT pa ON USER u
WHERE p.product_id = ‘iPhone’
AND u.usage_count > 0
AND u.account_created <= ‘2016-12-31’
AND u.account_created >= ‘2016-01-01’

到目前为止，我已经有了限制，但我不确定如何列出所需的信息。对于第一点 2016 年为 iPhone 购买的总分钟数，我可以在列名称后面添加 WHERE 语句吗？即E

SUM(us.purchase_minutes) WHERE p.product = 'iPhone'

我刚刚开始学习 SQL，所以如果这是一个基本问题，请原谅我。我在这篇文章中问了一个相关问题:How to write a select statement using a nested join

这是一张图片，希望能够更详细地描述数据库 /image/fsmoR.png

Answer 1

这很接近 - 可能存在拼写错误或语法错误，因为您没有提供一些示例数据，而且我现在没有简单的方法来测试。

但我认为这清楚地表明了教授希望你做什么——自己解决细节。

SELECT 
  u.user_id, 
  SUM(CASE WHEN p.product = 'iPhone' AND year(payment_date) = 2016 
           THEN p.purchase_call ELSE 0 END) AS q1,
  SUM(CASE WHEN p.product <> 'iPhone' AND year(payment_date) = 2016 
           THEN p.purchase_call ELSE 0 END) as q2,
  SUM(p.usage_winnings) as q3,
  SUM(CASE WHEN p.computer_usage = 0 THEN p.purchase_call ELSE 0 END) /
  SUM(CASE WHEN p.computer_usage = 1 THEN p.purchase_call ELSE 0 END) as q4 ,
  MAX(CASE WHEN p.purchase_call >= 2 THEN pa.payment_date ELSE null END) as q5,
  SUM(CASE WHEN pa.payment_type = 'D' THEN pa.amount ELSE 0 END) -
  SUM(CASE WHEN pa.payment_type = 'W' THEN pa.amount ELSE 0 END) as q6
FROM USER u
JOIN USAGE us ON u.user_id = us.user_id
JOIN PRODUCT p ON us.product_id = p.product_id  
JOIN PAYMENT pa ON u.user_id = pa.user_id
GROUP BY u.user_id