22,206
社区成员
发帖
与我相关
我的任务
分享
create table u01
(会议ID int, 酒店ID int, 会议日期 varchar(15))
insert into u01
select 1, 1, '2013-8-1' union all
select 2, 1, '2013-8-2' union all
select 3, 1, '2013-8-3' union all
select 4, 2, '2013-8-2' union all
select 5, 2, '2013-8-3' union all
select 6, 1, '2013-8-5'
select t.酒店ID,count(distinct t.d) '会议数'
from
(select 酒店ID,datename(d,会议日期)
-row_number() over(partition by 酒店ID order by cast(会议日期 as date)) 'd'
from u01) t
group by t.酒店ID
/*
酒店ID 会议数
----------- -----------
1 2
2 1
(2 row(s) affected)
*/