declare @rq char(10)
declare @end_rq char(10)
declare @s_rq char(10)
set @end_rq='2004-06-30'
set @s_rq='2004-06-01'
set @rq=@s_rq
while @rq<=@end_rq
begin
select sum(sshje) from daysphz
set @rq=convert(char(10),convert(datetime,@rq)+1,21)
end
上面语句应该这样的。写错了。呵呵~~~~~~~~~
declare @end_rq char(10)
declare @s_rq char(10)
set @end_rq='2004-06-30'
set @s_rq='2004-06-01'
set @rq=@s_rq
while @rq<=@end_rq
begin
select sum(sshje) from daysphz
set @rq=convert(char(10),convert(datetime,@rq)+1,21)
end
上面语句应该这样的。写错了。呵呵~~~~~~~~~
tSumn --插入单个表用
tSum1,tSum2,tSum3...,tSum31 --插入多表时用
declare @rq char(10)
declare @end_rq char(10)
declare @s_rq char(10)
set @end_rq='2004-06-30'
set @s_rq='2004-06-01'
set @rq=@s_rq
while @rq<=@end_rq
begin
insert tSum(fsum)
select sum(sshje) from daysphz
set @rq=convert(char(10),convert(datetime,@rq)+1,21)
end
declare @rq char(10)
declare @end_rq char(10)
declare @s_rq char(10)
set @end_rq='2004-06-30'
set @s_rq='2004-06-01'
set @rq=@s_rq
while @rq<=@end_rq
begin
select sum(sshje) into tSum1,tSum2,tSum3...,tSum31 from daysphz
set @rq=convert(char(10),convert(datetime,@rq)+1,21)
end 这样加好象不行啦~~~~~~~~~
declare @i int
set @i=1
declare @end_rq char(10)
declare @s_rq char(10)
set @end_rq='2004-06-30'
set @s_rq='2004-06-01'
set @rq=@s_rq
while @rq<=@end_rq
begin
exec('insert tSum'+convert(varchar(2),@i)+' (fSum) select sum(sshje) from daysphz')
set @rq=convert(char(10),convert(datetime,@rq)+1,21)
set @i=@i+1
end
select sum(sshje) from daysphz---是不是应该加个条件阿,
select sum(sshje) from daysphz where rq=@rq
--否则有什么意思阿
declare @end_rq char(10)
declare @s_rq char(10)
set @end_rq='2004-06-30'
set @s_rq='2004-06-01'
set @rq=@s_rq
while @rq<=@end_rq
begin
insert tSum(fsum)
select sum(sshje) from daysphz
set @rq=convert(char(10),convert(datetime,@rq)+1,21)
end 这样会提示:服务器: 消息 121,级别 15,状态 1,行 15
INSERT 语句的选择列表包含的项多于插入列表中的项。SELECT 语句中值的数目必须与 INSERT 语句中列的数目匹配。
declare @i int
set @i=1
declare @end_rq char(10)
declare @s_rq char(10)
set @end_rq='2004-06-30'
set @s_rq='2004-06-01'
set @rq=@s_rq
while @rq<=@end_rq
begin
exec('insert tSum'+convert(varchar(2),@i)+' (fSum) select sum(sshje) from daysphz')
set @rq=convert(char(10),convert(datetime,@rq)+1,21)
set @i=@i+1
end
错误提示:
服务器: 消息 156,级别 15,状态 1,行 12
在关键字 'convert' 附近有语法错误。
select sum(sshje) from daysphz---是不是应该加个条件阿,
select sum(sshje) from daysphz where rq=@rq
--否则有什么意思阿是呀。是应该加个条件,我写的那个只是举例,不记得写了。
insert tSum(fsum)
select sum(sshje) from daysphz
这不是一对一吗?---你把你具体的运行语句给发出来.
declare @end_rq char(10)
declare @s_rq char(10)set @end_rq='2004-06-03'
set @s_rq='2004-06-01'
set @rq=@s_rq
while @rq<=@end_rq
begin
insert #tmp_lbbj(xshe1)
select a.hzflbh,a.hzflchina,sum(sshje) as xshe
from hzfamx a,daysphz b,spkfk c
where b.spid=c.spid and substring(c.spbh,1,3)=a.hzflbh and rq=@rq
group by a.hzflbh,a.hzflchina order by hzflbh
set @rq=convert(char(10),convert(datetime,@rq)+1,21)
end
说明:#tmp_lbbj与hzfamx之间以hzflbh相对应!
#gmp_lbbj表的结构是这样的
hzflbh hzflchina xshe1 xshe2..............xshe31
101 书类
102 课本类
103 ....
104 ....
105
... ....
我本意是这样的,显示一条语句就会插入到xshe1这一列,依此类推!
declare @end_rq char(10)
declare @s_rq char(10)set @end_rq='2004-06-03'
set @s_rq='2004-06-01'
set @rq=@s_rq
while @rq<=@end_rq
begin
insert #tmp_lbbj(xshe1) ----这里有错 这里就一个字段
select a.hzflbh,a.hzflchina,sum(sshje) as xshe --这里有3个字段
from hzfamx a,daysphz b,spkfk c
where b.spid=c.spid and substring(c.spbh,1,3)=a.hzflbh and rq=@rq
group by a.hzflbh,a.hzflchina order by hzflbh
set @rq=convert(char(10),convert(datetime,@rq)+1,21)
end----
当然错了
insert #tmp_lbbj(xshe1) ----这里有错 这里就一个字段
select a.hzflbh,a.hzflchina,sum(sshje) as xshe --这里有3个字段--------------------insert #tmp_lbbj(对应a.hzflbh的字段,对应a.hzflchina字段,xshe1)
select a.hzflbh,a.hzflchina,sum(sshje) as xshe
declare @end_rq char(10)
declare @s_rq char(10)set @end_rq='2004-06-03'
set @s_rq='2004-06-01'
set @rq=@s_rq
while @rq<=@end_rq
begin
insert #tmp_lbbj(hzflbh,hzflchina,xshe1)
select a.hzflbh,a.hzflchina,sum(sshje) as xshe
from hzfamx a,daysphz b,spkfk c
where b.spid=c.spid and substring(c.spbh,1,3)=a.hzflbh and rq=@rq
group by a.hzflbh,a.hzflchina order by hzflbh
set @rq=convert(char(10),convert(datetime,@rq)+1,21)
end
我知道是这样样写的拉,但是他也只能插入到xshe1里呀,xshe2里是不可能有数据的,
在insert #tmp_lbbj(hzflbh,hzflchina,xshe1....xshe31) 这样加又不对,
INSERT 语句的选择列表包含的项少于插入列表中的项。SELECT 语句中值的数目必须与 INSERT 语句中列的数目匹配。
正在郁闷中.................
)
select a.hzflbh,a.hzflchina
,sum( case when '01'=right(convert(varchar(10),日期,21),2) then sshje1 else 0)
,sum( case when '02'=right(convert(varchar(10),日期,21),2) then sshje2 else 0)
...
,sum( case when '31'=right(convert(varchar(10),日期,21),2) then sshje31 else 0)
from hzfamx a,daysphz b,spkfk c
where b.spid=c.spid and substring(c.spbh,1,3)=a.hzflbh and rq=@rq
group by a.hzflbh,a.hzflchina order by hzflbh
insert #tmp_lbbj(hzflbh,hzflchina,xshe1,xshe2,...,xshe31
)
select a.hzflbh,a.hzflchina
,sum( case when '01'=right(convert(varchar(10),日期,21),2) then sshje1 else 0 end)
,sum( case when '02'=right(convert(varchar(10),日期,21),2) then sshje2 else 0 end)
...
,sum( case when '31'=right(convert(varchar(10),日期,21),2) then sshje31 else 0 end)
from hzfamx a,daysphz b,spkfk c
where b.spid=c.spid and substring(c.spbh,1,3)=a.hzflbh and rq=@rq
group by a.hzflbh,a.hzflchina order by hzflbh
declare @rq char(10)
declare @end_rq char(10)
declare @s_rq char(10)set @end_rq='2004-06-03'
set @s_rq='2004-06-01'insert #tmp_lbbj
select a.hzflbh,a.hzflchina
,[xshe1]=sum(case day(rq) when 1 then sshje else 0 end)
,[xshe2]=sum(case day(rq) when 2 then sshje else 0 end)
.......
,[xshe31]=sum(case day(rq) when 31 then sshje else 0 end)
from hzfamx a,daysphz b,spkfk c
where b.spid=c.spid
and substring(c.spbh,1,3)=a.hzflbh
and between @s_rq and @end_rq
group by a.hzflbh,a.hzflchina --,month(rq) --如果跨月,加上此分组
order by hzflbh
#gmp_lbbj表的结构是这样的
hzflbh hzflchina xshe1 xshe2..............xshe31
101 书类
就是标准的交叉表处理嘛.
declare @end_rq char(10)
declare @s_rq char(10)set @end_rq='2004-06-03'
set @s_rq='2004-06-01'insert #tmp_lbbj(hzflbh,hzflchina,[xshe1],[xshe2],[xshe3],[xshe4],[xshe5],[xshe6],[xshe7],[xshe8],[xshe9],[xshe10],[xshe11],[xshe12],[xshe13],[xshe14],[xshe15],[xshe16],[xshe17],[xshe18],[xshe19],[xshe20],[xshe21],[xshe22],[xshe23],[xshe24],[xshe25],[xshe26],[xshe27],[xshe28],[xshe29],[xshe30],[xshe31])
select a.hzflbh,a.hzflchina
,[xshe1]=sum(case day(rq) when 1 then sshje else 0 end)
,[xshe2]=sum(case day(rq) when 2 then sshje else 0 end)
,[xshe3]=sum(case day(rq) when 3 then sshje else 0 end)
,[xshe4]=sum(case day(rq) when 4 then sshje else 0 end)
,[xshe5]=sum(case day(rq) when 5 then sshje else 0 end)
,[xshe6]=sum(case day(rq) when 6 then sshje else 0 end)
,[xshe7]=sum(case day(rq) when 7 then sshje else 0 end)
,[xshe8]=sum(case day(rq) when 8 then sshje else 0 end)
,[xshe9]=sum(case day(rq) when 9 then sshje else 0 end)
,[xshe10]=sum(case day(rq) when 10 then sshje else 0 end)
,[xshe11]=sum(case day(rq) when 11 then sshje else 0 end)
,[xshe12]=sum(case day(rq) when 12 then sshje else 0 end)
,[xshe13]=sum(case day(rq) when 13 then sshje else 0 end)
,[xshe14]=sum(case day(rq) when 14 then sshje else 0 end)
,[xshe15]=sum(case day(rq) when 15 then sshje else 0 end)
,[xshe16]=sum(case day(rq) when 16 then sshje else 0 end)
,[xshe17]=sum(case day(rq) when 17 then sshje else 0 end)
,[xshe18]=sum(case day(rq) when 18 then sshje else 0 end)
,[xshe19]=sum(case day(rq) when 19 then sshje else 0 end)
,[xshe20]=sum(case day(rq) when 20 then sshje else 0 end)
,[xshe21]=sum(case day(rq) when 21 then sshje else 0 end)
,[xshe22]=sum(case day(rq) when 22 then sshje else 0 end)
,[xshe23]=sum(case day(rq) when 23 then sshje else 0 end)
,[xshe24]=sum(case day(rq) when 24 then sshje else 0 end)
,[xshe25]=sum(case day(rq) when 25 then sshje else 0 end)
,[xshe26]=sum(case day(rq) when 26 then sshje else 0 end)
,[xshe27]=sum(case day(rq) when 27 then sshje else 0 end)
,[xshe28]=sum(case day(rq) when 28 then sshje else 0 end)
,[xshe29]=sum(case day(rq) when 29 then sshje else 0 end)
,[xshe30]=sum(case day(rq) when 30 then sshje else 0 end)
,[xshe31]=sum(case day(rq) when 31 then sshje else 0 end)
from hzfamx a,daysphz b,spkfk c
where b.spid=c.spid
and substring(c.spbh,1,3)=a.hzflbh
and rq between @s_rq and @end_rq
group by a.hzflbh,a.hzflchina --,month(rq) --如果跨月,加上此分组
order by hzflbh
不好意思。我刚学SQL不久!