selecting every newest row for specific values

问题: Hi I have a table like so: SN | User | is_borrowed | date 105 | 1 | 1 |2019-1-1 105 | 1 | 0 |2019-2-1 105 | 1 | 1 |2019-3-1 106 |...

问题:

Hi I have a table like so:

SN  | User | is_borrowed | date
105 |  1   |      1      |2019-1-1
105 |  1   |      0      |2019-2-1
105 |  1   |      1      |2019-3-1
106 |  2   |      1      |2019-4-1
107 |  1   |      1      |2019-5-1
106 |  2   |      0      |2019-6-1
106 |  2   |      1      |2019-8-1
107 |  1   |      0      |2019-9-1
107 |  2   |      1      |2019-10-1

Wanted output is to show what is borrowed (not returned) and user=1:

SN  | User | is_borrowed | date
105 |  1   |      1      |2019-3-1

Output for User=2 and borrowed (not returned):

SN  | User | is_borrowed | date
106 |  2   |      1      |2019-8-1
107 |  2   |      1      |2019-10-1

In summary I want a list of devices currently borrowed that are not returned for each user. Sadly nothing comes to my mind tho :/


回答1:

looking to your expected result seems you are looking for the max(date) for SN, user where is_borrowed = 1

select SN, user, is_borrowed,  max(date)
from my_table 
where is_borrowed = 1 
group by  SN, user, is_borrowed

or for the device actual borrowed

Select * from my_table m
inner join t (
  select SN, is_borrowed,  max(date) max_date 
  from my_table 
  where is_borrowed = 1 
  group by  SN,  is_borrowed
) t on t.SN =  m.SN AND t.max_date = m.date

回答2:

SELECT t.sn, t.user, t.is_borrowed, t.date
FROM TABLE_NAME t
WHERE t.is_borrowed = 1
AND t.date =
              ( 
                  SELECT MAX(x.date)
                  FROM TABLE_NAME x
                  WHERE x.user = t.user
              )
AND (
     SELECT xx.is_borrowed
     FROM TABLE_NAME xx
     WHERE t.sn = xx.sn
     AND t.date < xx.date
     ) <> 0
;
  • 发表于 2019-01-09 08:40
  • 阅读 ( 207 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除