MySQL查询最新条目 [英] Mysql query most recent entry with a twist

问题描述

我将如何进行Mysql查询以获取每种类型的最新条目.然后计算它们并根据名称显示.谢谢

How would I make a Mysql query that would grab the most recent entry for each type. Then count them and display based off name. Thank you

MYsql数据库

Id | Date      | Type | Name  | Time  
1    11/8/2010 | 12312| Bill  | 7:30 AM  
2    11/8/2010 | 12311| Bill  | 7:45 PM  
3    11/8/2010 | 12312| Bill  | 8:30 AM  
4    11/8/2010 | 12310| Bill  | 9:40 AM  
5    11/8/2010 | 12312| Rich  | 7:30 AM  
6    11/8/2010 | 12312| Rich  | 7:45 PM  
7    11/8/2010 | 12312| Jim  | 8:30 AM  
8    11/8/2010 | 12310| Jim  | 9:40 AM  
9    11/8/2010 | 12312| Jim  | 7:30 AM  
10   11/8/2010 | 12310| Jim  | 7:45 AM  

mysql查询/php输出

mysql query/php output

Bill = 3    
2    11/8/2010 | 12311| Bill  | 7:45 PM  
3    11/8/2010 | 12312| Bill  | 8:30 AM  
4    11/8/2010 | 12310| Bill  | 9:40 AM  
Rich = 1  
6    11/8/2010 | 12312| Rich  | 7:45 PM  
Jim = 2  
7    11/8/2010 | 12312| Jim  | 8:30 AM  
8    11/8/2010 | 12310| Jim  | 9:40 AM  

推荐答案

SELECT  m.*
FROM    (
        SELECT  DISTINCT name, type
        FROM    mytable
        ) md
JOIN    mytable m
ON      m.id =
        (
        SELECT  id
        FROM    mytable mi
        WHERE   mi.name = md.name
                AND mi.type = md.type
        ORDER BY
                mi.name DESC, mi.type DESC, mi.date DESC, mi.id DESC
        LIMIT 1
        )

在mytable (name, type, date, id)上创建索引以使其快速运行.

Create an index on mytable (name, type, date, id) for this to work fast.

这篇关于MySQL查询最新条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！