请问句sql,数据格式(年月日,省份代码,城市代码,客户代码,购进量),求3-12月任一月份购进大于0但1、2月无购进的客户数 内附测试数据

请教句sql,数据格式(年月日,省份代码,城市代码,客户代码,购进量),求3-12月任一月份购进大于0但1、2月无购进的客户数 内附测试数据
年月日省份代码城市代码客户代码购进量
201001011111110
201001011111110
201001011111121
201002011111110
201002011111110
201002011111121
201003011111112
201003011111110
201003011111121
201004011111110
201004011111110
201004011111121
201005011111110
201005011111110
201005011111121
201006011111110
201006011111110
201006011111121
201007011111110
201007011111110
201007011111121
201008011111110
201008011111110
201008011111121
201009011111110
201009011111110
201009011111121
201010011111110
201010011111110
201010011111121
201011011111110
201011011111110
201011011111121
201012011111110
201012011111110
201012011111121


[解决办法]

SQL code

--> 测试数据: #tbif object_id('tempdb.dbo.#tb') is not null drop table #tbcreate table #tb (年月日 datetime,省份代码 int,城市代码 int,客户代码 int,购进量 int)insert into #tbselect '20100101',1,11,111,0 union allselect '20100101',1,11,111,0 union allselect '20100101',1,11,112,1 union allselect '20100201',1,11,111,0 union allselect '20100201',1,11,111,0 union allselect '20100201',1,11,112,1 union allselect '20100301',1,11,111,2 union allselect '20100301',1,11,111,0 union allselect '20100301',1,11,112,1 union allselect '20100401',1,11,111,0 union allselect '20100401',1,11,111,0 union allselect '20100401',1,11,112,1 union allselect '20100501',1,11,111,0 union allselect '20100501',1,11,111,0 union allselect '20100501',1,11,112,1 union allselect '20100601',1,11,111,0 union allselect '20100601',1,11,111,0 union allselect '20100601',1,11,112,1 union allselect '20100701',1,11,111,0 union allselect '20100701',1,11,111,0 union allselect '20100701',1,11,112,1 union allselect '20100801',1,11,111,0 union allselect '20100801',1,11,111,0 union allselect '20100801',1,11,112,1 union allselect '20100901',1,11,111,0 union allselect '20100901',1,11,111,0 union allselect '20100901',1,11,112,1 union allselect '20101001',1,11,111,0 union allselect '20101001',1,11,111,0 union allselect '20101001',1,11,112,1 union allselect '20101101',1,11,111,0 union allselect '20101101',1,11,111,0 union allselect '20101101',1,11,112,1 union allselect '20101201',1,11,111,0 union allselect '20101201',1,11,111,0 union allselect '20101201',1,11,112,1select count(distinct 客户代码 ) as Nfrom #tb twhere month(年月日)>2 and 购进量>0 and not exists(select 1 from #tb where 客户代码=t.客户代码 and month(年月日)<=2 and 购进量>0)N-----------1(1 行受影响)
[解决办法]
SQL code
create table tb (年月日 datetime,省份代码 int,城市代码 int,客户代码 int,购进量 int)insert into tbselect '20100101',1,11,111,0 union allselect '20100101',1,11,111,0 union allselect '20100101',1,11,112,1 union allselect '20100201',1,11,111,0 union allselect '20100201',1,11,111,0 union allselect '20100201',1,11,112,1 union allselect '20100301',1,11,111,2 union allselect '20100301',1,11,111,0 union allselect '20100301',1,11,112,1 union allselect '20100401',1,11,111,0 union allselect '20100401',1,11,111,0 union allselect '20100401',1,11,112,1 union allselect '20100501',1,11,111,0 union allselect '20100501',1,11,111,0 union allselect '20100501',1,11,112,1 union allselect '20100601',1,11,111,0 union allselect '20100601',1,11,111,0 union allselect '20100601',1,11,112,1 union allselect '20100701',1,11,111,0 union allselect '20100701',1,11,111,0 union allselect '20100701',1,11,112,1 union allselect '20100801',1,11,111,0 union allselect '20100801',1,11,111,0 union allselect '20100801',1,11,112,1 union allselect '20100901',1,11,111,0 union allselect '20100901',1,11,111,0 union allselect '20100901',1,11,112,1 union allselect '20101001',1,11,111,0 union allselect '20101001',1,11,111,0 union allselect '20101001',1,11,112,1 union allselect '20101101',1,11,111,0 union allselect '20101101',1,11,111,0 union allselect '20101101',1,11,112,1 union allselect '20101201',1,11,111,0 union allselect '20101201',1,11,111,0 union allselect '20101201',1,11,112,1select * from tb t where MONTH(年月日) between 3 and 12 and 购进量>0and not exists(select 1 from tb where 客户代码=t.客户代码 and MONTH(年月日)<=2 and 购进量>0)/*年月日    省份代码    城市代码    客户代码    购进量2010-03-01 00:00:00.000    1    11    111    2*/