create table cp
(
cpmc varchar(20),
cpbh varchar(20),
cprq varchar(20),
cpsj int
)
go
insert into cp values('bike','001','20100123',120)
insert into cp values('bike','001','20100223',300)
insert into cp values('bike','001','20100323',100)
insert into cp values('bike','001','20100120',80)
insert into cp values('shoe','002','20100423',100)
insert into cp values('shoe','002','20101120',80)
go
selectcpmc,cpbh
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201001%') as '1月'
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201002%') as '2月'
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201003%') as '3月'
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201004%') as '4月'
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201005%') as '5月'
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201006%') as '6月'
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201007%') as '7月'
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201008%') as '8月'
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201009%') as '9月'
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201010%') as '10月'
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201011%') as '11月'
,(select case when sum(cpsj) is NULL then '0' else sum(cpsj) end from cp where cp.cpbh=t.cpbh and cp.cpmc=t.cpmc and cprq like '201012%') as '12月'
from cp as t
group by cpmc,cpbh
请问一下老师cp.cpbh=t.cpbh and cp.cpmc=t.cpmc取到了是什么作用呢？
既然有cp as t
那么cp.cpbh=t.cpbh and cp.cpmc=t.cpmc不是自已等于自己吗？
还有如果没有cp.cpbh=t.cpbh and cp.cpmc=t.cpmc，就会出现重复的，
创建的语句我都已在上面，希望老师也运行一下，看一下效果
至于原因，不是很明白，请高手指教。
|
