的Mysql按日期排序和group by结果不同

Question

我的表结构如下

id parent last_date  sub_type 
---------------------------------- 
11 9  2017-02-28 1101 
10 9  2016-08-26 1101 
8 1  2017-02-20 1101 
12 12  2016-08-31 1102  
14 12  2016-12-20 1102  
9 9  2016-12-31 1101 
13 12  2017-03-23 1102 
2 1  2017-01-25 1101 
1 1  2016-12-31 1101 

我想基于日期（最长的第一）各sub_type读取行。我想下面的查询

SELECT * FROM mytable GROUP BY sub_type ORDER BY ISNULL(last_date) DESC, last_date DESC 

并且它导致

id parent last_date sub_type  
-------------------------------- 
1 1  2016-12-31 1101  
12 12  2016-08-31 1102  

但我希望下面的结果。

id parent last_date sub_type  
-------------------------------- 
13 12  2017-03-23 1102 
11 9  2017-02-28 1101  

请指导我以取得以上结果。

编辑：

LAST_DATE可能有NULL值将超过日最大项的优先级。这就是为什么我选择ISNULL DESC命令。

Answer 1

您可以在WHERE子句中使用ORDER BY和LIMIT 1中的相关子查询来查找您正在查找的行的ID。

SELECT * 
FROM mytable t1 
WHERE id = (
    SELECT id 
    FROM mytable t2 
    WHERE t2.sub_type = t1.sub_type 
    ORDER BY ISNULL(last_date) DESC, last_date DESC, id DESC 
    LIMIT 1 
) 

演示：http://rextester.com/DSPH11524

注：sub_type应该被索引。

Answer 2

这是一个典型的问题，通过聚合获取每个组中的一条记录。试试这个：

select 
    mytable.* 
from mytable 
join (
    select max(last_date) as last_date, sub_type from mytable group by sub_type 
) t1 on mytable.sub_type = t1.sub_type and mytable.last_date = t1.last_date 

看到这篇文章How to select the first/least/max row per group in SQL。

和相关链接右：

Retrieving the last record in each group。

and demo in sqlfiddle。

编辑：

如果没有2相同的最后日期和空优先级，那么试试这个：

select 
    mytable.* 
from mytable 
join (
    select 
     max(last_date) as last_date, 
     max(isnull(last_date)) nulled, 
     sub_type 
    from mytable 
    group by sub_type 
) t1 on mytable.sub_type = t1.sub_type 
and (t1.nulled and mytable.last_date is null or (t1.nulled <> 1 and mytable.last_date = t1.last_date)) 

也demo在sqlfiddle。

Answer 3

您也可以通过根据sub_type列和last_date列的降序给出行号来完成此操作。

查询

select t1.`id`, t1.`parent`, t1.`last_date`, t1.`sub_type` from 
(
    select `id`, `parent`, `last_date`, `sub_type`, 
    (
     case `sub_type` when @A 
     then @R := @R + 1 
     else @R := 1 and @A := `sub_type` end 
    ) as `rn` 
    from `your_table_name` t, 
    (select @R := 0, @A := '') r 
    order by `sub_type`, `last_date` desc 
)t1 
where t1.`rn` = 1; 

Sql Fiddle demo

Answer 4

你已经写了错误的查询。

指定where条件后的条件where条款

以下查询会给出您的预期结果。

SELECT id,parent,max(last_Date),sub_type FROM mytable GROUP BY sub_type