问题描述
我有一个名为页面, 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!