Mysql查询Group by Last record and first record

Question

我有 2 个表，状态表与配置文件表相连。 Profile 表存储唯一的 tagId，status 表存储多个重复的 tagid 条目。我想按tagid的最后一条记录和第一条记录分组显示。

表:简介注意:tagId是唯一的

--------------------------------------------------------------------
| tagId(PK) | blah2 | blah3 | blah4 |
--------------------------------------------------------------------
101         |
102         |
103         |
104         |
105         |
106         |

表:状态

--------------------------------------------------------------------
statusId | tagId |  date      | height| weight |  statusType | blah2 | 
--------------------------------------------------------------------
1        | 101   | 2010-01-01 | 5.6   |  300   |  single     |
2        | 102   | 2010-01-01 | 5.7   |  300   |  single     | 
3        | 101   | 2015-01-01 | 5.6   |  310   |  married    |
4        | 103   | 2010-01-01 | 5.6   |  300   |  single     |
5        | 104   | 2010-01-01 | 5.6   |  300   |  single     |
6        | 101   | 2016-01-01 | 5.6   |  300   |  pregnant   |
7        | 101   | 2016-09-01 | 5.6   |  300   |  delivery   |
8        | 105   | 2010-01-01 | 5.6   |  300   |  single     |

我想尝试按第一个日期分组并按最后一个状态类型分组

查询结果将是:

--------------------------------------------------------------------
| tagId |  date      | height| weight |  statusType | blah2 | 
--------------------------------------------------------------------
| 101   | 2010-01-01 | 5.6   |  300   |  delivery   |
| 102   | 2010-01-01 | 5.7   |  300   |  single     | 
| 103   | 2010-01-01 | 5.6   |  300   |  single     |
| 104   | 2010-01-01 | 5.6   |  300   |  single     |
| 105   | 2010-01-01 | 5.6   |  300   |  single     |

但我无法成功，我尝试使用此 MySQL 代码

SELECT DISTINCT Profile.TagId,Status.date,Status.StatusType,Status.height,Status.weight FROM Profile 
LEFT JOIN Status ON Status.TagId = Profile.TagId 
Where Status.StatusId In( Select Max(Status.StatusId) From Status Group By      Status.TagId ) 
Group By Status.TagId ORDER BY Profile.TagId ASC, Status.TagId DESC

但它会返回最后的日期和最后的状态类型，就像这样

查询结果:

--------------------------------------------------------------------
| tagId |  date      | height| weight |  statusType | blah2 | 
--------------------------------------------------------------------
| 101   | 2016-09-01 | 5.6   |  300   |  delivery   |
| 102   | 2010-01-01 | 5.7   |  300   |  single     | 
| 103   | 2010-01-01 | 5.6   |  300   |  single     |
| 104   | 2010-01-01 | 5.6   |  300   |  single     |
| 105   | 2010-01-01 | 5.6   |  300   |  single     |

Answer 1

我想您可能正在寻找这样的东西:

SELECT DISTINCT p.tagId, 
                smin.`date`, smin.height, smin.weight,
                smax.StatusType 
FROM Profile AS p
LEFT JOIN (
   SELECT tagId, MAX(`date`) AS max_date, MIN(`date`) AS min_date 
   FROM Status
   GROUP BY tagId 
) AS s ON p.tagId = s.tagId
LEFT JOIN Status AS smin ON smin.tagId = p.tagId AND s.min_date = smin.`date`
LEFT JOIN Status AS smax ON smax.tagId = p.tagId AND s.max_date = smax.`date`

查询使用一个派生表，该表返回每个 tagId 的最小和最大 date 值。使用这些值，我们可以连接回 Status 表并从 中获取 date、height 和 weight 值>Status 记录具有 minimum date 值 和 StatusType 值 Status 记录具有 maximum date 值。