php - 来自多个表的 MySQL 计数列。按主要 ID 对结果进行分组

Question

我有以下 3 个 MySQL 表:

products

| id | name      | comment_count ||----|-----------|---------------|| 1  | Product A | 10            || 2  | Product B | 20            || 3  | Product C | 30            |

products_views

| product_id | pv_count | pv_date       || -----------|-------|------------|| 1          | 10    | 2015-01-01 || 1          | 10    | 2015-01-02 || 1          | 10    | 2015-01-03 || 2          | 20    | 2015-01-01 || 2          | 20    | 2015-01-02 || 2          | 20    | 2015-01-03 || 3          | 30    | 2015-01-01 || 3          | 30    | 2015-01-02 || 3          | 30    | 2015-01-03 |

products_likes

| product_id | pl_count | pl_date       || -----------|-------|------------|| 1          | 10    | 2015-01-01 || 1          | 10    | 2015-01-02 || 1          | 10    | 2015-01-03 || 2          | 20    | 2015-01-01 || 2          | 20    | 2015-01-02 || 2          | 20    | 2015-01-03 || 3          | 30    | 2015-01-01 || 3          | 30    | 2015-01-02 || 3          | 30    | 2015-01-03 |

我要加在一起products.comment_count + product_views.count + product_likes.count按 products.id 分组, 其中product_views.pv_date和 product_likes.pl_date在 2015-01-01 之间和 2015-01-03 .通过 total 订购.

我想要的是:

| product_id | total || -----------|-------|| 3          | 210   || 2          | 140   || 1          | 70    |

Answer 1

试试这个查询

SELECT 
   p.product_id, 
  (p.comment_count + SUM(pv.count) + SUM(pl.count)) AS total
 FROM products p
 JOIN products_views pv ON( p.product_id = pv.product_id)
 JOIN products_likes pl ON( p.product_id = pl.product_id)
 WHERE pl.date BETWEEN '2015-01-01' AND '2015-01-03'
  AND pv.date BETWEEN '2015-01-01' AND '2015-01-03'
 GROUP BY p.product_id   
 ORDER BY total DESC