34,587
社区成员
发帖
与我相关
我的任务
分享
declare @a table(InDate datetime not null )
insert @a select '2007-12-25'
insert @a select '2007-12-24'
insert @a select '2007-11-12'
insert @a select '2006-10-1'
insert @a select '2005-9-1'
select a,b from (
select a=case when b is null then a+'年' else '' end,
b=case when b is null then '' else b+'月' end,
c=a+case when b is null then '90' else b end from(
select ltrim(year(indate)) a,right('0'+ltrim(month(indate)),2) b
from @a
group by ltrim(year(indate)),right('0'+ltrim(month(indate)),2)
)bb group by a,b with rollup
)cc where a is not null order by c desc
--result
/*a b
-------------- ------
2007年
12月
11月
2006年
10月
2005年
09月
*/
哦换这种:
declare @t table (InDate datetime)
insert into @t select '2007-12-25'
insert into @t select '2007-12-24'
insert into @t select '2007-11-12'
insert into @t select '2006-10-1'
insert into @t select '2005-9-1'
declare @b table(id int identity(1,1),writer varchar(50),parentid varchar(10),lev int)
insert into @b select distinct datepart(yy,indate),'',1 from @t order by datepart(yy,indate) desc
insert into @b select distinct datepart(mm,a.indate),b.id,2 from @t a,@b b
where b.writer=datepart(yy,a.indate)
order by datepart(mm,a.indate) desc
select * from @b
1。declare @t table (InDate datetime)
insert into @t select '2007-12-25'
insert into @t select '2007-12-24'
insert into @t select '2007-11-12'
insert into @t select '2006-10-1'
insert into @t select '2005-9-1'
declare @b table(writer varchar(50),parentid varchar(10),lev int)
insert into @b select distinct datepart(yy,indate),'',1 from @t order by datepart(yy,indate) desc
insert into @b select distinct datepart(mm,a.indate),b.writer,2 from @t a,@b b
where b.writer=datepart(yy,a.indate)
order by datepart(mm,a.indate) desc
select * from @b
--问题1
create table tb(InDate datetime not null )
go
insert tb select '2007-12-25'
insert tb select '2007-12-24'
insert tb select '2007-11-12'
insert tb select '2006-10-1'
insert tb select '2005-9-1'
go
select yy = case when mm = (select min(mm) from
(
select distinct datename(yy,indate) + '年' yy , mm = '' from tb
union all
select distinct datename(yy,indate) + '年' yy , datename(mm,indate) + '月' mm from tb
) t
where t.yy = m.yy) then yy else '' end,
mm
from
(
select distinct datename(yy,indate) + '年' yy , mm = '' from tb
union all
select distinct datename(yy,indate) + '年' yy , datename(mm,indate) + '月' mm from tb
) m
drop table tb
/*
yy mm
-------------------------------- --------------------------------
2005年
09月
2006年
10月
2007年
11月
12月
(7 行受影响)
*/
2。。
declare @t table (InDate datetime)
insert into @t select '2007-12-25'
insert into @t select '2007-12-24'
insert into @t select '2007-11-12'
insert into @t select '2006-10-1'
insert into @t select '2005-9-1'
declare @date datetime
set @date=getdate()
select case datepart(dw,indate)
when '1' then '星期日'
when '2' then '星期一'
when '3' then '星期二'
when '4' then '星期三'
when '5' then '星期四'
when '6' then '星期五'
when '7' then '星期六' end xq
from @t where datepart(ww,indate)=datepart(ww,@date) and datediff(dd,indate,@date)>=1