with cte as ( select convert(varchar(6),日期,112) date,datename(week,日期) [week], count(*) cnt,rid = row_number() over (partition by convert(varchar(6),日期,112) order by datename(week,日期)) from tb group by convert(varchar(6),日期,112),datename(week,日期) )select * from cte where rid = 1
-- 2000select convert(varchar(6),日期,112) date,datename(wk,日期) [week],count(*) cnt into #t from tb group by convert(varchar(6),日期,112),datename(week,日期)select * from #t t where not exists (select 1 from #t where date = t.date and [week] < t.[week])drop table #t
-- 2000select convert(varchar(6),日期,112) date,datename(wk,日期) [week],count(*) cnt into #t from tb group by convert(varchar(6),日期,112),datename(week,日期) ---这句的意思是向临时表#t中插入月份和星期,按照月份和星期分组得到数值select * from #t t where not exists (select 1 from #t where date = t.date and [week] < t.[week]) --这句话的意思是得到第一个星期的值
with cte as
(
select convert(varchar(6),日期,112) date,datename(week,日期) [week],
count(*) cnt,rid = row_number() over (partition by convert(varchar(6),日期,112) order by datename(week,日期))
from tb
group by convert(varchar(6),日期,112),datename(week,日期)
)select *
from cte
where rid = 1
-- 2000select convert(varchar(6),日期,112) date,datename(wk,日期) [week],count(*) cnt
into #t
from tb
group by convert(varchar(6),日期,112),datename(week,日期)select *
from #t t
where not exists (select 1 from #t where date = t.date and [week] < t.[week])drop table #t
into #t
from tb
group by convert(varchar(6),日期,112),datename(week,日期)
---这句的意思是向临时表#t中插入月份和星期,按照月份和星期分组得到数值select *
from #t t
where not exists (select 1 from #t where date = t.date and [week] < t.[week])
--这句话的意思是得到第一个星期的值