分组依据没有给我最新的组

发布于 2024-12-10 10:25:23 字数 529 浏览 0 评论 0原文

我试图从我的表中获得不同的结果，人们说我应该使用分组依据。这工作了一半...我现在得到了不同的结果，但结果不是最新的线程...我的表包含几栋建筑物的公寓的状态。由于它是历史表，因此可以多次找到公寓...我需要进行选择以检索具有当前状态的不同公寓。

ID    Building Apartment_id  Status
1     1        1             1
2     1        1             2
3     2        2             3
4     2        4             2
5     2        3             2
6     2        5             1
7     2        6             1

我目前正在与：

SELECT * FROM `ib30_history` GROUP BY apartment_id, building ORDER BY id DESC

原文

I was trying to get the distinct result from my table, and people said I should use the group by. This worked half way... I now get the distinct result but the result is not the newest thread... my table contains status on apartments from several buildings. The apartments can be found many times since it's a history table... I need to make a select that retrieves the distinct apartments with the current status.

ID    Building Apartment_id  Status
1     1        1             1
2     1        1             2
3     2        2             3
4     2        4             2
5     2        3             2
6     2        5             1
7     2        6             1

I'm currently working with:

SELECT * FROM `ib30_history` GROUP BY apartment_id, building ORDER BY id DESC

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

地狱即天堂 2024-12-17 10:25:23

SELECT 
  Building
  , Appartment_id
  , Status 
FROM ib30_history a
WHERE id = ( SELECT MAX(id) FROM ib30_history b 
             WHERE b.Building = a.Building AND b.Appartment_id = a.Appartment_id)

SELECT 
  Building
  , Appartment_id
  , Status 
FROM ib30_history a
WHERE id = ( SELECT MAX(id) FROM ib30_history b 
             WHERE b.Building = a.Building AND b.Appartment_id = a.Appartment_id)

回复收藏 0 原文

待＂谢繁草 2024-12-17 10:25:23

 select h.apartment_id, h.status
  from history h 
       join (select apartment_id, max(status) status
               from history
              group by apartment_id) recent 
       on h.apartment_id = recent.apartment_id 
      and h.status = recent.status

 select h.apartment_id, h.status
  from history h 
       join (select apartment_id, max(status) status
               from history
              group by apartment_id) recent 
       on h.apartment_id = recent.apartment_id 
      and h.status = recent.status

回复收藏 0 原文

~没有更多了~