来自多个表的 MySQL 计数列.按主 ID 对结果进行分组 - MySQL count column from multiple tables. Group results by primary id

MySQL count column from multiple tables. Group results by primary id

本文关键字：结果 ID 按主数列 MySQL | 更新日期: 2023-09-27

我有以下 3 个 MySQL 表：

products

|身份证 |姓名 |comment_count ||----|-----------|---------------||1 |产品 A |10 ||2 |产品 B |20 ||3 |产品 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.id products.comment_count + product_views.count + product_likes.count分组，其中product_views.pv_date和product_likes.pl_date在2015-01-01和2015-01-03之间。按total订购。

我想要什么：

|product_id |总计 ||-----------|-------||3 |210 ||2 |140 ||1 |70 |

尝试此查询

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

SELECT 
    (products.comment_count+products_views.pv_count+products_likes.pl_count) AS    product_count
    FROM products 
    INNER JOIN products_views.product_id=products.id
    INNER JOIN products_likes.product_id=products.id
    WHERE  (product_views.pv_date BETWEEN dateVal AND dateVal) 
    AND (products_likes.pl_date BETWEEN dateVal AND dateVal) 
   GROUP BY     products.id
   ORDER BY  product_count

在联接之前执行"每个表"的计数，否则您将面临获得比预期更大的结果的风险，因为联接可能会乘以行数。此外，由于某些产品可能不存在于喜欢或视图中，因此建议对两个"派生表"进行左外联接。

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

顺便说一句：date和count在大多数SQL方言中是保留词;将列命名为date或count不是一个好主意;使用更多的东西，例如date_entered，date_created，view_count，like_count等。

select p.product_id, sum(p.comment_count) + sum(v.count) + sum(l.count) as total from products p join products_views v on p.product_id = v.product_id join products_likes l on p.product_id = l.product_id group by p.product_id
where l.date between '2015-01-01' and '2015-01-03'
order by total desc

试试这个