当前位置: 代码迷 >> Sql Server >> 请问句sql,数据格式(年月日,省份代码,城市代码,客户代码,购进量),求3-12月任一月份购进大于0但1、2月无购进且省份代码为1的客户数,内附测试数据,多谢
  详细解决方案

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

热度:503   发布时间:2016-04-27 14:35:20.0
请教句sql,数据格式(年月日,省份代码,城市代码,客户代码,购进量),求3-12月任一月份购进大于0但1、2月无购进且省份代码为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
20100101 2 21 211 0
20100101 2 21 211 0
20100101 2 21 212 1
20100201 2 21 211 0
20100201 2 21 211 0
20100201 2 21 212 1
20100301 2 21 211 2
20100301 2 21 211 0
20100301 2 21 212 1
20100401 2 21 211 0
20100401 2 21 211 0
20100401 2 21 212 1
20100501 2 21 211 0
20100501 2 21 211 0
20100501 2 21 212 1
20100601 2 21 211 0
20100601 2 21 211 0
20100601 2 21 212 1
20100701 2 21 211 0
20100701 2 21 211 0
20100701 2 21 212 1
20100801 2 21 211 0
20100801 2 21 211 0
20100801 2 21 212 1
20100901 2 21 211 0
20100901 2 21 211 0
20100901 2 21 212 1
20101001 2 21 211 0
20101001 2 21 211 0
20101001 2 21 212 1
20101101 2 21 211 0
20101101 2 21 211 0
20101101 2 21 212 1
20101201 2 21 211 0
20101201 2 21 211 0
20101201 2 21 212 1




想要得到的结果:
格式:(省份代码,城市代码,客户数)
结果:(1,11,1)

------解决方案--------------------
SQL code
if object_id('tempdb.dbo.#tb') is not null drop table #tbgocreate 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 城市代码=t.城市代码 and 客户代码=t.客户代码 and month(年月日)<=2 and 购进量>0)group by 省份代码,城市代码省份代码        城市代码        N----------- ----------- -----------1           11          1(1 行受影响)
------解决方案--------------------
SQL code