复杂sql

-- 行转列(利用group by+case when)
select
gender,
sum(case when birth_date>'2000/01/01 00:00:00' AND birth_date<'2001/01/01 00:00:00' then 1 else 0 end ) as '2000年出生',
sum(case when birth_date>'2001/01/01 00:00:00' AND birth_date<'2002/01/01 00:00:00' then 1 else 0 end ) as '2001年出生'
from student
group by gender;

-- 连续3天登录（生成rowNumber，登录日期减rowNumber生成一个新日期列，分组判断数量是否大于3）
WITH t1 AS (
SELECT DISTINCT ID,LoginDate,Name FROM LoginLog
),
t2 AS(
SELECT ID,Name,LoginDate,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY LoginDate) rn FROM t1
),
t3 AS(
SELECT ID,Name,LoginDate,DATEADD(day,-rn,LoginDate) tempDate FROM t2
)
SELECT DISTINCT Name FROM t3 GROUP BY tempDate,Name HAVING COUNT(tempDate)>=3