请问句sql,数据格式(年月日,省份代码,城市代码,客户代码,购进量),求3-12月任一月份购进大于0但1、2月无购进的客户数 内附测试数据
日期：2014-05-18 浏览次数：20571 次
请教句sql,数据格式(年月日,省份代码,城市代码,客户代码,购进量),求3-12月任一月份购进大于0但1、2月无购进的客户数 内附测试数据
年月日 省份代码 城市代码 客户代码 购进量
20100101 1 11 111 0
20100101 1 11 111 0
20100101 1 11 112 1
20100201 1 11 111 0
20100201 1 11 111 0
20100201 1 11 112 1
20100301 1 11 111 2
20100301 1 11 111 0
20100301 1 11 112 1
20100401 1 11 111 0
20100401 1 11 111 0
20100401 1 11 112 1
20100501 1 11 111 0
20100501 1 11 111 0
20100501 1 11 112 1
20100601 1 11 111 0
20100601 1 11 111 0
20100601 1 11 112 1
20100701 1 11 111 0
20100701 1 11 111 0
20100701 1 11 112 1
20100801 1 11 111 0
20100801 1 11 111 0
20100801 1 11 112 1
20100901 1 11 111 0
20100901 1 11 111 0
20100901 1 11 112 1
20101001 1 11 111 0
20101001 1 11 111 0
20101001 1 11 112 1
20101101 1 11 111 0
20101101 1 11 111 0
20101101 1 11 112 1
20101201 1 11 111 0
20101201 1 11 111 0
20101201 1 11 112 1
------解决方案--------------------
年月日 省份代码 城市代码 客户代码 购进量
20100101 1 11 111 0
20100101 1 11 111 0
20100101 1 11 112 1
20100201 1 11 111 0
20100201 1 11 111 0
20100201 1 11 112 1
20100301 1 11 111 2
20100301 1 11 111 0
20100301 1 11 112 1
20100401 1 11 111 0
20100401 1 11 111 0
20100401 1 11 112 1
20100501 1 11 111 0
20100501 1 11 111 0
20100501 1 11 112 1
20100601 1 11 111 0
20100601 1 11 111 0
20100601 1 11 112 1
20100701 1 11 111 0
20100701 1 11 111 0
20100701 1 11 112 1
20100801 1 11 111 0
20100801 1 11 111 0
20100801 1 11 112 1
20100901 1 11 111 0
20100901 1 11 111 0
20100901 1 11 112 1
20101001 1 11 111 0
20101001 1 11 111 0
20101001 1 11 112 1
20101101 1 11 111 0
20101101 1 11 111 0
20101101 1 11 112 1
20101201 1 11 111 0
20101201 1 11 111 0
20101201 1 11 112 1
------解决方案--------------------
- SQL code
--> 测试数据: #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 行受影响)
------解决方案--------------------
- SQL code
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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
