求一条Sql语句的写法。

Question

哦。不是取一条数据，要把B和C也取出来，就是RoomClass不能有重复的。

Answer 1

select * from 表 as a where not exists (select * from 表 where RoomClass=a.RoomClass and CreateDate>a.CreateDate)

Answer 2

或:
select * from 表 as a,(select RoomClass,max(CreateDate) as BigTime from 表 group by RoomClass) as b where a.RoomClass=b.RoomClass and a.CreateDate=b.Bigtime

Answer 3

select * from testb  a  where createdate=(select max(createdate) from testb where roomclass=a.roomclass) order by a.roomclass

Answer 4

测试结果RoomClass MinV        MaxV        CreateDate                                             
--------- ----------- ----------- ------------------------------------------------------ 
A         3           11          1905-05-27 00:00:00.000
B         6           8           1905-05-28 00:00:00.000
C         3           21          1905-05-29 00:00:00.000

Answer 5

测试结果
RoomClass MinV        MaxV        CreateDate                                             
--------- ----------- ----------- ------------------------------------------------------ 
A         4           10          2004-12-21 00:00:00.000
B         6           8           2004-12-19 00:00:00.000
C         3           21          2004-12-18 00:00:00.000

Answer 6

select 表.* from 表,(select RoomClass,max(createDate) maxcreatdate Group by RoomClass) as a
where a.RoomClass=表.RoomClass and creatdate=maxcreatdate

Answer 7

select * from 表 as a where not exists (select 1 from 表 where RoomClass=a.RoomClass and CreateDate>a.CreateDate)把*改成1效率应该更快，反正不需要返回列，只是作个判断

Answer 8

select a.* from 表 a
right join (select RoomClass,max(CreateDate) CreateDate from 表 group by RoomClass) b 
on a.RoomClass=b.RoomClass and a.CreateDate=b.CreateDate