最佳查询评级数据库？

I have a table of ratings that stores a user ID, object ID, and a score (+1 or -1). Now, when I want to display a list of objects with their total scores, the number of +1 votes, and the number of -1 votes.

我有一个评级表,用于存储用户ID,对象ID和分数(+1或-1)。现在,当我想显示一个对象列表时,他们的总得分,+1票数和-1票数。

How can I do this efficiently without having to do SELECT COUNT(*) FROM rating WHERE (score = +/-1 AND object_id = ..)? That's two queries per object displayed, which is unacceptable. Is the database design reasonable?

如何有效地执行此操作而无需执行SELECT COUNT(*)FROM rating WHERE(score = +/- 1 AND object_id = ..)?这是每个对象显示的两个查询,这是不可接受的。数据库设计合理吗?

3 个解决方案

#1

While it doesn't address your question of reasonable design, here's a query that gets you both counts at once:

虽然它没有解决您的合理设计问题,但这里有一个查询可以同时为您提供两个计数:

select
    sum(case when score = 1 then 1 else 0 end) 'positive'
,   sum(case when score = -1 then 1 else 0 end) 'negative'
,   objectId
from
    ratings
where
    objectId = @objectId ...
group by
    objectId

#2

This should do it:

这应该这样做:

SELECT
    UserID, ObjectID,
    SUM(CASE WHEN score=1 Then 1 Else 0 End) as UpVotes,
    SUM(CASE WHEN score=-1 Then 1 Else 0 End) as DownVotes,
FROM YourTable
GROUP BY UserID, ObjectID

#3

select object_id, 
       sum(case when score = 1 then 1 else 0) upvotes, 
       sum(case when score = -1 then -1 else 0) downvotes,
       sum(score)
from ratings
group by object_id

Perhaps something like that.

也许是这样的。

#1