开发者

MySQL: group by `topic_id`, if `topic_id` > 0

I have entries which might or might not be assigned to a topic.

I want to select entries with the highest score by summing up the score of those, which belong to the same topic. In addition, I want to get a number of similar entries, which were grouped together.

SELECT *, COUNT(`topic_id`) FROM `entries`
GROUP BY `topic_id` HAVING `topic开发者_开发知识库_id` > 0
ORDER BY SUM(`score`) DESC LIMIT 30

This query misses a few things. Firstly, I want entries without topic_id (topic_id = 0) not to be grouped, but to be treated individually. Secondly, COUNT(topic_id) does not always return a real number of entries belonging to the same topic.


SELECT * FROM
(    
    SELECT topic_id,COUNT(*),SUM(`score`) AS sum
    FROM `entries`
    WHERE `topic_id` > 0
    GROUP BY `topic_id`
UNION
    SELECT topic_id, 1,1 AS sum
    FROM `entries`
    WHERE `topic_id` = 0
)
ORDER BY sum DESC LIMIT 30


SELECT
    `topic_id` > 0 IsGroupedTopid,
    if(`topic_id`>0, `topic_id`, id) TopicOrEntryId,
    COUNT(*) CountEntries,
    SUM(`score`) TotalScore
FROM `entries`
GROUP BY
    `topic_id` > 0,
    if(`topic_id`>0, `topic_id`, id)
ORDER BY SUM(`score`) DESC
LIMIT 30;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜