sql server

select convert(varchar(7), getdate(), 23)//2006-05

select datediff(week, '2020-11-29', getdate())//上周天到周六

select datediff(week, dateadd(dd, +1, '2020-11-29'), dateadd(dd, +1, getdate()))//上周六到周五

//查询多列中的最大值
select rb.rbxx_id, (select max(MaxJd) from (values (rb.zzm_stj),(rb.zzm_xtj),(rb.zzm_yg)) as #temp(MaxJd)) as maxJd
from d_xxgl_rbxx rb
where rb.deleted_flag = 0

//查询近7天的日期

select dateadd(day, number, convert(date,dateadd(dd, -6, getdate()), 23)) as rq
from master.dbo.spt_values
where type = 'P' and number <![CDATA[ <= ]]> DATEDIFF(day, convert(date, dateadd(dd, -6, getdate()), 23), getdate())

//让ID从1重新开始增加

dbcc checkident('d_ht_htgk',reseed,0)

//查询所有上级/下级

//上级
with test_student(stu_id, stu_name)
as
(
select stu_id, stu_name
from sys_student
where school_id = ‘1’ and del_flag = 0
union all
select a.parent_id, a.stu_id, a.stu_name
from sys_student a
inner join
test_student b
on a.stu_id=b.parent_id where a.del_flag = 0
)
select stu_id, stu_name from test_student order by stu_id
//下级
with test_student(stu_id, parent_id,stu_name)
as
(
select s.stu_id, s.parent_id, s.stu_name
from sys_student s
where s.school_id = ‘1’ and s.deleted_flag = 0
union all
select a.stu_id, a.parent_id, a.stu_name
from sys_student a
inner join
test_student b
on a.parent_id=b.stu_id where a.deleted_flag = 0
)
select stu_id, stu_name from test_student order by stu_id