按值和最大日期划分的MySQL组

Question

我需要提取列result_1和result_2的最后一个字符，并对它们进行分组，这样我就有了一个从0到9的列表，指示何时生成。在折叠代码中，我设法从0到9获得了一个列表，但是日期是表中最早的日期，而不是最近的日期。

$sql = "SELECT * FROM ((SELECT RIGHT(result_1, 1) AS results, date 
    FROM act_results ORDER BY date ASC) 
    UNION ALL (SELECT RIGHT(result_2, 1) AS results, date 
    FROM act_results)) s WHERE results != ''";

$sql .= " GROUP BY results";

$table = mysql_query($sql);

while ($row = mysql_fetch_array($table)) {
   echo $row['results']." (".$row['date'].") <br>";
}

有什么办法解决吗？提前谢谢。

Answer 1

将group by与max()结合使用

SELECT results, max(date)
FROM ((SELECT RIGHT(result_1, 1) AS results, date
       FROM act_results
      ) UNION ALL
      (SELECT RIGHT(result_2, 1) AS results, date
       FROM act_results
      )
     ) s
WHERE results <> ''
GROUP BY results;