使用 DISTINCT(几列)和 MAX(另一列)计算行 [英] Count rows with DISTINCT(several columns) and MAX(another column)
问题描述
我的表格包含用户对不同项目的投票.它有以下列:
My table contains votes of users for different items. It has the following columns:
id, user_id, item_id, vote, utc_time
只有 id 是唯一字段,user_id 和 utc_time 的组合可能也是唯一的.但用户可以多次为任何项目投票.
Only id is a unique field and the combination of user_id and utc_time is probably also unique. But user can cast votes for any item many times.
投票不是数字,而是具有多个可能值之一(例如,糟糕"、糟糕"、好"、极好").
A vote is not a number but rather has one of several possible values (e.g., "awful", "bad", "good", "excellent").
我需要计算有多少不同的用户为给定的#item#投了最后一票,如优秀"、良好"等.假设我只有四种不同的可能投票值,我需要获取包含以下字段的四条记录:
I need to count how many different users cast their last vote for a given #item# as "excellent", as "good", etc. So assuming I have only four different possible vote values, I need to get four records with the following fields:
vote, count_of_users
我了解如何计算所有投票,而不仅仅是用户的最后投票:
I understand how to count all votes, not only last votes of users:
SELECT vote, COUNT(id) FROM votes WHERE item_id=#item# GROUP BY vote;
但我不知道如何只计算每个用户 utc_time = MAX(utc_time) 的投票数...感谢您的帮助.
But I cannot figure out how to count only the votes where utc_time = MAX(utc_time) for each user... Thanks for your help.
这个问题与我之前的问题有关:选择一行 MAX(column) 为已知的其他几列没有子查询
This question is connected to the previous question of mine: Select one row with MAX(column) for known other several columns without subquery
推荐答案
试试这个解决方案,如果它适合你,
try this solution if it fits with you,
SELECT a.item_ID,
SUM(CASE WHEN a.vote = 'awful' THEN 1 ELSE 0 END) awful,
SUM(CASE WHEN a.vote = 'bad' THEN 1 ELSE 0 END) bad,
SUM(CASE WHEN a.vote = 'good' THEN 1 ELSE 0 END) good,
SUM(CASE WHEN a.vote = 'excellent' THEN 1 ELSE 0 END) excellent
FROM tableName a
INNER JOIN
(
SELECT user_ID, MAX(utc_time) max_time
FROM tableName
GROUP BY user_ID
) b ON a.user_ID = b.user_ID AND
a.utc_time = b.max_time
-- WHERE a.item_ID = 'valueHere'
GROUP BY a.item_ID
更新 1
SELECT a.item_ID,
a.vote,
COUNT(*) totalCount
FROM tableName a
INNER JOIN
(
SELECT user_ID, MAX(utc_time) max_time
FROM tableName
WHERE item_id = 'valueHere'
GROUP BY user_ID
) b ON a.user_ID = b.user_ID AND
a.utc_time = b.max_time
GROUP BY a.vote
这篇关于使用 DISTINCT(几列)和 MAX(另一列)计算行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!