MySQL 子查询/相关查询

Question

我正在构建一个用户统计信息页面并尝试正确显示统计信息，并希望得到一些帮助。

我有一个为注册用户提供可下载文件的网站，我想跟踪下载情况，每个用户下载该文件的次数以及他们从中下载了多少独特的产品。

表：customer_statistics

| user |  product_id  |  file_download  |  date_accessed   |
------------------------------------------------------------
| tom  |  1104        |  mp3       |  2017-05-06 00:00:00  |
| tom  |  1048        |  video     |  2017-05-06 00:00:00  |
| tom  |  1048        |  video     |  2017-05-06 00:00:00  |
| tom  |  1048        |  video     |  2017-05-07 00:00:00  |
| tom  |  1048        |  video     |  2017-05-08 00:00:00  |
| tom  |  1010        |  video     |  2017-05-08 00:00:00  |
| tom  |  1077        |  video     |  2017-05-08 00:00:00  |
| sue  |  1749        |  photo     |  2017-05-09 00:00:00  |
| sue  |  1284        |  video     |  2017-05-09 00:00:00  |
| sue  |  1284        |  video     |  2017-05-09 00:00:00  |
| sue  |  1065        |  mp3       |  2017-05-09 00:00:00  |
| sue  |  1344        |  video     |  2017-05-10 00:00:00  |
| sue  |  2504        |  photo     |  2017-05-10 00:00:00  |

我会这样显示数据：

Name | Unique Mp3s | Unique Photos | Unique Videos | Total
----------------------------------------------------------
Tom  |    1        |    0          |    3          |  7
Sue  |    1        |    2          |    2          |  6

列unique mp3s/photos/videos显示从每种文件类型下载的唯一product_id数，而total显示该特定用户的所有文件类型和出现次数的总数。

实现上述结果的最佳方法是什么？

提前感谢一堆！

Answer 1

我会这样做，不需要子查询：

SELECT user AS `Name`,
  COUNT(DISTINCT CASE file_download WHEN 'mp3' THEN product_id END) AS `Unique Mp3s`
  COUNT(DISTINCT CASE file_download WHEN 'photo' THEN product_id END) AS `Unique Photos`
  COUNT(DISTINCT CASE file_download WHEN 'video' THEN product_id END) AS `Unique Videos`
  COUNT(DISTINCT product_id) AS `Total`
FROM customer_statistics
GROUP BY user;