在单个SQL语句中使用多个COUNT和SUM [英] Using multiple COUNTs and SUMs in a single SQL statement

问题描述

我有一个名为页面， page_views ， page_items 和 page_votes 的表.后三个表包含一个 page_id 外键，以记录属于页面的每个视图，项目和投票.

I have a tables called pages, page_views, page_items and page_votes. The latter three tables contain a page_id foreign key in order to keep a record of each individual view, item and vote that belongs to a page.

查询页面时，我还想检索 COUNT个页面浏览量， COUNT个页面条目和 SUM page_votes.vote .

When I query a page, I also want to retrieve COUNT page_views, COUNT page_items and SUM page_votes.vote.

我在下面粘贴了一个查询.它检索视图总数.我已经尝试过添加项目并对其进行投票，但是结果可能是语法错误或视图/项目/投票以相同且错误的"数字返回，这可能是由于我加入的方式所致.

I have pasted a query below. It retrieves the total number of views. I've made various attempts to add items and votes to it, but the result is either a syntax error or views/items/votes returned as an identical and "wrong" number, probably due to the way I am joining.

如何为该查询添加项目和投票?

How can I add items and votes to this query?

SELECT
  Page.*,
  COUNT(*) AS views
FROM pages AS Page 
INNER JOIN page_views AS PageView
  ON Page.id = PageView.page_id 
GROUP BY Page.id 
ORDER BY views DESC   
LIMIT 10 OFFSET 0

推荐答案

这将选择TOP 10 viewed个页面，并将仅对这些页面的项目和投票计数.

This will select TOP 10 viewed pages, and will count items and votes only for these pages.

如果您有很多页面但只需要10，则效率很高，从而消除了不必要的计数.

Efficient if you have lots of pages but need only 10 of them, eliminates unneeded counting.

SELECT  (
        SELECT COUNT(*)
        FROM   page_views
        WHERE  page_views.page_id = pages.id
        ) AS views_count,
        (
        SELECT COUNT(*)
        FROM   page_items
        WHERE  page_items.page_id = pages.id
        ) AS items_count,
        COALESCE(
        (
        SELECT SUM(vote)
        FROM   page_votes
        WHERE  page_votes.page_id = pages.id
        ), 0) AS votes_sum
FROM    pages
ORDER BY
        views_count DESC
LIMIT 10

更高效的查询:

SELECT  pages.*,
        (
        SELECT COUNT(*)
        FROM   page_items
        WHERE  page_items.page_id = pages.id
        ) AS items_count,
        COALESCE(
        (
        SELECT SUM(vote)
        FROM   page_votes
        WHERE  page_votes.page_id = pages.id
        ), 0) AS votes_sum
FROM    (
        SELECT  page_id, COUNT(*) AS cnt
        FROM    page_views
        GROUP BY
                page_id
        ORDER BY cnt DESC
        LIMIT 10
        ) AS pvd,
        pages
WHERE  pages.id = pvd.page_id

，消除了与pages不必要的连接.

, eliminates unneeded joins with pages.

这篇关于在单个SQL语句中使用多个COUNT和SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！