sql中一个字段在多个条件下查询并且生成多列

Question

有distrib、mbr、date三列。需要查询不同date条件下1-3月和4-6月的mbr会员数量，并且输出1-3月 as a和4-6月as b两列，且group by distrib

Answer 1

with t1 as
(
--去重后的distrib
select distinct(distrib) distrib from table1 where DATEPART(M,date)>=1 and DATEPART(M,date)<=6
),t2 as
(
--1-3月数据
select distrib,COUNT(mbr) as a from table1 where DATEPART(M,date)>=1 and DATEPART(M,date)<=3
group by distrib
),t3 as
(
--4-6月数据
select distrib,COUNT(mbr) as b from table1 where DATEPART(M,date)>=4 and DATEPART(M,date)<=6
group by distrib
)select t1.distrib,t2.a,t3.b from t1
left join t2 on t1.distrib=t2.distrib
left join t3 on t1.distrib=t3.distrib