sql中一个字段在多个条件下查询并且生成多列
有distrib、mbr、date三列。需要查询不同date条件下1-3月和4-6月的mbr会员数量,并且输出1-3月asa和4-6月asb两列,且groupbydist...
有distrib、mbr、date三列。需要查询不同date条件下1-3月和4-6月的mbr会员数量,并且输出1-3月 as a和4-6月as b两列,且group by distrib
展开
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
(
--去重后的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
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询