count相加 sqlserver_在单个SQL语句中使用多个COUNT和SUM

这将选择前10个浏览过的页面,并将仅对这些页面计数项目和投票.

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

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

,消除了不必要的页面联接.