查询出一个表里 bookid不重复并且时间是最新的数据
例如： booksid datetime
1 10:00:00
1 12:00:00
2 07:00:00
2 11:00:00
我只想要 1 12:00:00 和 2 11:00:00 数据
我该怎么办 各位大神前来帮帮忙
拜托了
------解决方案--------------------
- SQL code
select * from tb t where datetime=(select max(datetime) from tb where bookid=t.bookid)
------解决方案--------------------
- SQL code
select * from tb t where [datetime]=(select max([datetime]) from tb where bookid=t.bookid)
------解决方案--------------------
- SQL code
select A.*from TB Awhere not exists(select 1 from TB B where A.booksid = B.booksid and A.datetime <B.datetime)
------解决方案--------------------
select b.booksid,b.datetime from table as b join
(
select booksid,max(datetime) from table as A group by booksid) as c
on b.booksid=c.booksid and b.datetime=c.datetime
ls几位的也可以的,很多种办法
------解决方案--------------------
select b.booksid,b.datetime from table as b join
(
select booksid,max(datetime)as datetime from table as A group by booksid) as c
on b.booksid=c.booksid and b.datetime=c.datetime
------解决方案--------------------
- SQL code
create table ny(booksid int,dt time) insert into nyselect 1, '10:00:00' union allselect 1, '12:00:00' union allselect 2, '07:00:00' union allselect 2, '11:00:00'select booksid,dtfrom(select *,row_number() over(partition by booksid order by dt desc) rn from ny) ywhere rn=1/*booksid dt----------- ----------------1 12:00:00.00000002 11:00:00.0000000(2 row(s) affected)*/