请教SQL

[smartlylife]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hotel_sendtime]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hotel_sendtime]
GO

CREATE TABLE [dbo].[hotel_sendtime] (
[id] [varchar] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hotel_id] [varchar] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Hotel_name] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[sendtime] [datetime] NOT NULL ,
[sendtype] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[groupid] [varchar] (36) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[group_name] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hotel_status] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hotel_logout] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

我想要的结果是根据hotel_id分组,取得分组中最后一行信息并根据sendtime倒序,不知道这个SQL如何写,先谢过.

还有一个是按天为单位,查询出每天内同一个hotel_id出现的次数并取得每天内的同一个hotel_id的最后一条(按sendtime)记录,记录按sendtime倒序,再次谢过

[yjdn]

我想要的结果是根据hotel_id分组,取得分组中最后一行信息并根据sendtime倒序,不知道这个SQL如何写,先谢过.

--你的ID字段是自增的吧？
select * from hotel_sendtime where id in
(
select id=max(id) from hotel_sendtime group by hotel_id
)
order by sendtime desc

--如果ID不是自增，那如果Sendtime的时间是不断加大且唯一的话，可用SendTime代替ID

[yjdn]

还有一个是按天为单位,查询出每天内同一个hotel_id出现的次数并取得每天内的同一个hotel_id的最后一条(按sendtime)记录,记录按sendtime倒序,再次谢过


--select 日期=left(sendtime,10),次数=count(lotel_id),最后=max(sendtime)
from hotel_sendtime
group by left(sendtime,10)
order by left(sendtime,10) desc

[duanduan1122]

mark

[子陌红尘]

1、
select
a.*
from
hotel_sendtime a
where
not exists(select
1
from
hotel_sendtime
where
hotel_id=a.hotel_id
and
id!=a.id
and
sendtime>a.sendtime)
order by
a.sendtime desc


2、
select
a.*,
times = (select
count(*)
from
hotel_sendtime
where
hotel_id=a.hotel_id
and
datediff(d,sendtime,a.sendtime)=0)
from
hotel_sendtime a
where
not exists(select
1
from
hotel_sendtime
where
hotel_id=a.hotel_id
and
datediff(d,sendtime,a.sendtime)=0
and
id!=a.id
and
sendtime>a.sendtime)
order by
a.sendtime desc

[点点星灯]

--参考，是按name分组，取时间最大的那一个
declare @tab table(id int,name varchar(20),reversion varchar(20),uploadtime datetime)

insert @tab(id,name,reversion,uploadtime)
values(1,'csdn','A','2005-05-21')
insert @tab(id,name,reversion,uploadtime)
values(2,'csdn','B','2005-05-22')
insert @tab(id,name,reversion,uploadtime)
values(3,'donews','A','2005-05-23')
insert @tab(id,name,reversion,uploadtime)
values(4,'donews','C','2005-05-27')

select * from @tab


select * from @tab tem
where uploadtime=(select max(uploadtime) from @tab where name=tem.name)