日期:2014-05-18 浏览次数:20621 次
--> 测试数据: #tb if object_id('tempdb.dbo.#tb') is not null drop table #tb create table #tb (年月日 datetime,省份代码 int,城市代码 int,客户代码 int,购进量 int) insert into #tb select '20100101',1,11,111,0 union all select '20100101',1,11,111,0 union all select '20100101',1,11,112,1 union all select '20100201',1,11,111,0 union all select '20100201',1,11,111,0 union all select '20100201',1,11,112,1 union all select '20100301',1,11,111,2 union all select '20100301',1,11,111,0 union all select '20100301',1,11,112,1 union all select '20100401',1,11,111,0 union all select '20100401',1,11,111,0 union all select '20100401',1,11,112,1 union all select '20100501',1,11,111,0 union all select '20100501',1,11,111,0 union all select '20100501',1,11,112,1 union all select '20100601',1,11,111,0 union all select '20100601',1,11,111,0 union all select '20100601',1,11,112,1 union all select '20100701',1,11,111,0 union all select '20100701',1,11,111,0 union all select '20100701',1,11,112,1 union all select '20100801',1,11,111,0 union all select '20100801',1,11,111,0 union all select '20100801',1,11,112,1 union all select '20100901',1,11,111,0 union all select '20100901',1,11,111,0 union all select '20100901',1,11,112,1 union all select '20101001',1,11,111,0 union all select '20101001',1,11,111,0 union all select '20101001',1,11,112,1 union all select '20101101',1,11,111,0 union all select '20101101',1,11,111,0 union all select '20101101',1,11,112,1 union all select '20101201',1,11,111,0 union all select '20101201',1,11,111,0 union all select '20101201',1,11,112,1 select count(distinct 客户代码 ) as N from #tb t where month(年月日)>2 and 购进量>0 and not exists(select 1 from #tb where 客户代码=t.客户代码 and month(年月日)<=2 and 购进量>0) N ----------- 1 (1 行受影响)
------解决方案--------------------
create table tb (年月日 datetime,省份代码 int,城市代码 int,客户代码 int,购进量 int) insert into tb select '20100101',1,11,111,0 union all select '20100101',1,11,111,0 union all select '20100101',1,11,112,1 union all select '20100201',1,11,111,0 union all select '20100201',1,11,111,0 union all select '20100201',1,11,112,1 union all select '20100301',1,11,111,2 union all select '20100301',1,11,111,0 union all select '20100301',1,11,112,1 union all select '20100401',1,11,111,0 union all select '20100401',1,11,111,0 union all select '20100401',1,11,112,1 union all select '20100501',1,11,111,0 union all select '20100501',1,11,111,0 union all select '20100501',1,11,112,1 union all select '20100601',1,11,111,0 union all select '20100601',1,11,111,0 union all select '20100601',1,11,112,1 union all select '20100701',1,11,111,0 union all select '20100701',1,11,111,0 union all select '20100701',1,11,112,1 union all select '20100801',1,11,111,0 union all select '20100801',1,11,111,0 union all select '20100801',1,11,112,1 union all select '20100901',1,11,111,0 un