1 -- 判断指定日期是星期几
2 select
3 etl_dt
4 ,case when dayofweek(etl_dt) = 1 then 7 else dayofweek(etl_dt) - 1 end as week1 -- 周一至周七
5 ,datediff(etl_dt,'1970-01-05') % 7 + 1 as week2 -- 周一至周七
6 ,case when dayofweek(etl_dt) = 1 then 0 else dayofweek(etl_dt) - 1 end as week3 -- 周日为0
7 ,datediff(etl_dt,'1970-01-04') % 7 as week4 -- 周日为0
8 ,dayofweek(etl_dt) as week5 -- 周日为1
9 ,datediff(etl_dt,'1970-01-04') % 7 + 1 as week6 -- 周日为1
10 from (
11 select '2018-04-16' as etl_dt union all
12 select '2018-04-17' as etl_dt union all
13 select '2018-04-18' as etl_dt union all
14 select '2018-04-19' as etl_dt union all
15 select '2018-04-20' as etl_dt union all
16 select '2018-04-21' as etl_dt union all
17 select '2018-04-22' as etl_dt
18 ) t1
19 order by etl_dt
20 ;
