重复运用循环语句，用到cast (ab as varchar) 转换字符
--------------------------------------------------------------------------------------------------
DECLARE @txddate VARCHAR(10),@barcode nvarchar(13),@unitcode nvarchar(20),@price float ;
DECLARE @sql1 varchar(8000),@sql2 varchar(8000);
Create Table aList(countercode varchar(20), txddate varchar(10),barcode nvarchar(13),unitcode nvarchar(20), price float)
Declare Cur_Table Cursor For
select barcode,unitcode,price from product --where barcode='6937617066546' and unitcode='GNMC10'
Open Cur_Table
Fetch Next From Cur_Table Into @barcode,@unitcode,@price
while @@Fetch_Status = 0
begin
set @sql1='select countercode,txddate,barcode,unitcode,price
from transactionlog where txdtype=''NS'' and type=''n'' and price<'''+cast(@price*0.5 as varchar) +'''
and
barcode='''+@barcode+''' and
unitcode='''+@unitcode+''' and txddate like ''%1205%'''
--print @sql1
set @sql2='insert into aList '
+@sql1 exec (@sql2)
Fetch Next From Cur_Table Into @barcode,@unitcode,@price
end
Close Cur_Table
Deallocate Cur_Table
select b1.regionname as '大区', d1.bakNvarchar1 as '代理商', a1.countercode as '专柜编码'
,b1.countername as '专柜名称' ,a1.txddate as '日期',c1.chinesename as '名称' ,c1.mclassname as '中类',
c1.price as '原价',a1.price as '折价' from aList a1 ,counters as b1,product AS c1,Jml_Data.dbo.DA_UserList_Modify as d1 where
a1.countercode=b1.countercode and c1.barcode=a1.barcode and c1.unitcode=a1.unitcode
and b1.countername not like '%虚拟%' and d1.userID in
(SELECT superiorID FROM Jml_Data.dbo.DA_UserList_Modify where bakNvarchar1 in
(select baname from bainfo where countercode =a1.countercode and flag=2))
order by b1.regionname,d1.bakNvarchar1,a1.txddate
--drop table aList
