TO:her_totem(青争岁月)SELE KHMC,DH,LB,BM,MC,DW,SUM(SL) AS SL,AVG(DJ) AS DJ,SUM(JE) AS JE FROM XSTJ INTO CURS TJB ;
WHERE &CXTJ GROUP BY KHMC,DH,BM ORDER BY KHMC,DH,BM
这个是在VFP下写的,如果在SQL的存储过程里写?
哪个宏怎么写???
WHERE &CXTJ GROUP BY KHMC,DH,BM ORDER BY KHMC,DH,BM
这个是在VFP下写的,如果在SQL的存储过程里写?
哪个宏怎么写???
1、把select语句去掉AVG,sum,
2、根据关键值求avg,sum
哪些都好办,只是WHERE 后面怎么写???用VFP只要用&CXTJ就行了。SQL怎么写?
符合sql而非vfp。如不清楚动态SQL语句,可查sp_executesql 或 execute()。
cxtj=iIF(EMPTY(m.BM),'1=1'," BM='"+allt(M.BM)+"'");
+iif(EMPTY(M.MC),''," and MC='"+allt(M.MC)+"'";
+iif(EMPTY(m.RQ1),'',"and RQ>="+allt(str(M.RQ1));
+iif(EMPTY(m.RQ2),'',"and RQ<="+allt(str(M.RQ2))
create procedure yourpro
(
...
@cxtj varchar(2000)='1=1',
...
)
as
begin
declare @sql varchar(4000)
select @sql='SELEct * INTO #TJB WHERE '+@CXTJ
select @sql=@sql+' SELEct distinct a.KHMC,a.DH,a.BM,a.mc,a.dw,b.SL,b.DJ,b.je from #TJB a left join (select KHMC,DH,BM, SUM(SL) AS SL,AVG(DJ) AS DJ,SUM(JE) AS JE from #TJB GROUP BY KHMC,DH,BM) b on a.khmc=b.khmc and a.dh=b.dh and a.bm=b.bm order by a.KHMC,a.DH,a.BM '
sp_executesql @sql
... ...
end
没有充分考虑重复记录问题,可能表设计需要进一步优化,这里仅提供一个模式。
cxtj=iIF(EMPTY(m.BM),'1=1'," BM='"+allt(M.BM)+"'");
+iif(EMPTY(M.MC),''," and MC='"+allt(M.MC)+"'");
+iif(EMPTY(m.RQ1),'',"and RQ>="+allt(str(M.RQ1)));
+iif(EMPTY(m.RQ2),'',"and RQ<="+allt(str(M.RQ2)))
declare TJB cursor for
select * from XSTJ
如果where子句中的条件是动态生成的,那么就只能使用动态sql了
即定义一个字符串,然后把要运行的语句组合成字符串赋给该变量,最后执行。
如:
declare @str varchar(8000)
set @str = 'declare TJB cursor for select KHMC,DH,LB,BM,MC,DW,SUM(SL) AS SL,AVG(DJ) AS DJ,SUM(JE) AS JE from XSTJ where ' + @CXTJ + ' GROUP BY KHMC,DH,BM ORDER BY KHMC,DH,BM'
exec @str
谢谢!问题已解决。(这年头吃素的狼比吃荤的还厉害)TO:bluepower2008(蓝色力量)
谢谢你的贴子。