MySQL按最大日期分组

Question

我有一个名为votes的表格，它有四个列：id、name、choice和date。

****id****name****vote******date***
****1*****sam*******A******01-01-17
****2*****sam*******B******01-05-30
****3*****jon*******A******01-01-19

我的最终目标是统计所有的投票数，但我只想每个人算一票，并且只算每个人最近的一次投票。

在上面的例子中，结果应该是A得到1票，B也得到1票。

这是我目前拥有的内容：

select name,
    sum(case when uniques.choice = A then 1 else 0 end) votesA,
    sum(case when uniques.choice = B then 1 else 0 end) votesB
FROM (
    SELECT id, name, choice, max(date)
    FROM votes
    GROUP BY name
) uniques;

然而，这样做是行不通的，因为子查询确实选择了最大日期，但它没有包括与该最大日期相关联的正确选项。

- Sam

---

你可以发布期望的输出吗？ - kc2018

选票数A-1.......选票数B-1.... 我只在选择中包括name以进行调试。 - Sam

Answer 1

不要使用"group by"来获取最近的投票。 考虑使用join或其他选项。 这是一种方法：

SELECT v.name,
       SUM(v.choice = 'A') as votesA,
       SUM(v.choice = 'B') as votesB
FROM votes v
WHERE v.date = (SELECT MAX(v2.date) FROM votes v2 WHERE v2.name = v.name)
GROUP BY v.name;

这里是一个 SQL Fiddle，可以通过 这个链接 访问。

- Gordon Linoff

---

当我尝试这样做时，我得到了以下错误信息：WHERE子句中的未知列v.name。 - Sam

根据您的问题，“name”在“votes”表中，因此该列应该存在。 - Gordon Linoff

抱歉，我只是想看看子查询的作用。我不太确定你的查询输出的含义。它是一个有三列的表：名称、票数A和票数B。因此，它包括了Sam对A和B的投票。 - Sam

@Sam...并不像SQL Fiddle演示的那样。我不知道你做错了什么。 - Gordon Linoff

使用MYSQL_FULL_GROUP_BY（在5.7和8.0中引入）时，这种方法不再适用。但是，如果将GOUP_BY v.name移动到子查询中，它就可以再次正常工作了;-) - Meloman

Answer 2

你的答案很接近，但需要使用JOIN自身。

子查询通过名称获取Max日期，然后JOIN自身。

select
    sum(case when T.vote = 'A' then 1 else 0 end) votesA,
    sum(case when T.vote = 'B' then 1 else 0 end) votesB
FROM (
    SELECT name,Max(date) as date
    FROM T
    GROUP BY name
) AS T1 INNER JOIN T ON T1.date = T.date

SQLFiddle

SQLFiddle

- D-Shih

Answer 3

基于@d-shish的解决方案，并且自MySQL 5.7引入ONLY_FULL_GROUP_BY以来，GROUP BY语句必须像这样放置在子查询中：

SELECT v.`name`,
    SUM(v.`choice` = 'A') as `votesA`,
    SUM(v.`choice` = 'B') as `votesB`
FROM `votes` v
WHERE (
    SELECT MAX(v2.`date`) 
    FROM `votes` v2 
    WHERE v2.`name` = v.`name` 
    GROUP BY v.`name` #           << after
) = v.`date`
# GROUP BY v.`name`               << before

否则，它将不再起作用！

- Meloman

Answer 4

试试这个

SELECT
    choice,
    COUNT(1)
FROM
    votes v
    INNER JOIN
    (
        SELECT
            id,
            max(date)
        FROM
            votes
        GROUP BY
            name
    ) tmp ON
    v.id = tmp.id
GROUP BY
    choice;

- Edgar Romero

Answer 5

select 
name,
sum( case when choice = 'A' then 1 else 0 end) voteA,
sum( case when choice = 'B' then 1 else 0 end) voteB
from
(
select id, name, choice
from votes
where date = (select max(date) from votes t2 
                where t2.name = votes.name ) 
) t
group by name

或者只输出一行，显示VoteA和VoteB的总数：

select 
sum( case when choice = 'A' then 1 else 0 end) voteA,
sum( case when choice = 'B' then 1 else 0 end) voteB
from
(
select id, name, choice
from votes
where date = (select max(date) from votes t2 
                where t2.name = votes.name ) 
) t

- kc2018

Answer 6

如果你真的只需要计算一个人的最后一次投票，可以这样做：

SELECT 
  sum(case when vote='A' then cnt else 0 end) voteA,
  sum(case when vote='B' then cnt else 0 end) voteB
FROM 
 (SELECT vote,count(distinct name) cnt
  FROM (
      SELECT name,vote,date,max(date) over (partition by name) maxd
      FROM votes
  ) 
  WHERE date=maxd
  GROUP BY vote
 )

附注：MySQL v8

- Vasyl Moskalov