获取组SQL的最后一个条目

I have in my database 3 tables. One for the Apps, other for the historic of the state of the apps and a last table with the description of each state.

我在我的数据库中有3个表。一个用于应用程序,另一个用于应用程序状态的历史记录,以及带有每个状态描述的最后一个表。

Table Apps:

ID   Name
1    App1
2    App2 

Table Historic:

ID   IDApp  IDState  DateChanged
1    1      2        2016-06-01
2    1      4        2016-06-07
3    2      1        2016-06-05
4    2      2        2016-06-12 

Table State:

ID  Description
1   Open
2   Working
3   Pending
4   Closed 

I want a query that returns the last state of each App. I want the return like this:

我想要一个返回每个App的最后状态的查询。我想要这样的回报:

Name   Description    Date
App1   Closed         2016-06-07
App2   Working        2016-06-12

2 个解决方案

#1

You should consider making your DateChanged field a date/time field if there can be multiple possible states witnessed for a given app on a single day. As-is this should work but state reporting for a given app for a given day will arbitrarily choose the status with the highest ID if there are > 1 status witnessed for a given app on a the most recent day reported in history.

如果在一天内针对给定应用程序可见多个可能的状态,则应考虑将DateChanged字段设置为日期/时间字段。现在应该可以工作,但是如果在历史上报告的最近一天中给定应用程序有> 1个状态,那么给定日期的给定应用程序的状态报告将任意选择具有最高ID的状态。

SELECT a.Name, 
    COALESCE(s.Description, '(No History)') as Description, 
    h.DateChanged as Date
FROM Apps a LEFT JOIN (
    SELECT IDApp, 
        MAX(IDState) as IDState, -- arbitrary tie breaker for > 1 state in a day
        DateChanged
    FROM Historic h1 INNER JOIN (
        SELECT IDApp, MAX(DateChanged) as MaxDateChanged
        FROM Historic
        GROUP BY IDApp
    ) h2 ON h1.IDApp = h2.IDApp
        AND h1.DateChanged = h2.MaxDateChanged
    GROUP BY IDApp, DateChanged
) h ON a.ID = h.IDApp
LEFT JOIN State s ON s.ID = h.IDState

#2

SELECT Name, Description, MAX(DateChanged) from Apps 
INNER JOIN Historic ON Apps.ID=Historic.IDapp 
INNER JOIN State ON State.ID=Historic.IDState 
GROUP BY Name, Description