mysql - 高级 MySQL 组查询

Question

场景:我很难构建单个查询来显示给定架构的报告。据说，要显示的报告应该遵循基于乘数的计算。例如: 用户、消息数 x 乘数

需要什么:mysql查询将显示预期的查询结果。

* schema *
table: users
id | name
 1 |  cris
 2 |  dave
 3 |  jona

table: services
id | name
 1 |  standard
 2 |  premium

table: service_users
user_id | service_id | multiplier
 1      |  1         |  1
 1      |  2         |  2
 2      |  1         |  1
 2      |  2         |  2
 3      |  1         |  1
 3      |  2         |  2

table: messages
user_id | service_id | content
 1      |  1         |  howdy
 1      |  1         |  hey
 1      |  1         |  hello
 1      |  2         |  the quantity
 1      |  2         |  insignificant
 2      |  1         |  pill
 2      |  1         |  dock
 2      |  2         |  misty docks
 3      |  1         |  drive
 3      |  2         |  with style
 3      |  2         |  like a hawk
 3      |  2         |  earthling rise

查询结果应如下所示

 user_id | multiplier(1) | multiplier(2) | total
  1     |  3            |  4            |  7
  2     |  2            |  2            |  4
  3     |  1            |  6            |  7

Answer 1

SELECT users.id,
       COUNT( IF( service_users.multiplier = 1, 1, NULL ) AS m1,
       COUNT( IF( service_users.multiplier = 2, 1, NULL ) AS m2
       FROM users, messages, service_users
       WHERE users.id = messages.user_id AND
             messages.user_id = service_users.user_id
             messages.service_id = service_users.service_id
       GROUP BY users.id