比较难的SQL语句，看来只能用存储过程了。(50分)

zyt_1978 · 2008-09-27

业务员展位费  会务费    会刊广告费  场地广告费  小王    1200     800      300         800  小李   2600     1200       500        1200 费用类型名称    起始金额    终止金额   提成比例   展位费          500         1000          25   展位费          1000        2000          30   展位费          2000        3000          35   会务费          500         1000          25   会务费          1000        2000          30   会务费          2000        3000          35   会刊广告费        500         1000          25   会刊广告费        1000        2000          30   会刊广告费        2000        3000          35 场地广告费        500         1000          25   场地广告费        1000        2000          30   场地广告费        2000        3000          35 怎样得到业务员的提成

conlin · 2008-09-27

没有搞懂LZ要表达得问题。[?]

kaida · 2008-09-27

select [业务员],   [展位费提成] = [展位费] * t2.[提成比例] / 100,   [会务费提成] = [会务费] * t3.[提成比例] / 100,   [会刊广告费提成] = [会刊广告费] * t4.[提成比例] / 100,   [场地广告费提成] = [场地广告费] * t5.[提成比例] / 100,   [合计]=isnull([展位费] * t2.[提成比例] / 100,0) +          isnull([会务费] * t3.[提成比例] / 100,0) +          isnull([会刊广告费] * t4.[提成比例] / 100,0) +          isnull([场地广告费] * t5.[提成比例] / 100,0) from Table1 t1 left join Table2 t2 on(t2.[费用类型名称]='展位费' and t2.[起始金额]<=t1.[展位费] and t2.[终止金额]>=t1.[展位费])   left join Table2 t3 on(t3.[费用类型名称]='会务费' and t3.[起始金额]<=t1.[会务费] and t3.[终止金额]>=t1.[会务费])   left join Table2 t4 on(t4.[费用类型名称]='会刊广告费' and t4.[起始金额]<=t1.[会刊广告费] and t4.[终止金额]>=t1.[会刊广告费])   left join Table2 t5 on(t5.[费用类型名称]='场地广告费' and t5.[起始金额]<=t1.[场地广告费] and t5.[终止金额]>=t1.[场地广告费])

zyt_1978 · 2008-09-28

create   proc proc_ywytc   @proid varchar(50) as   /*   exec proc_ywytc   create proc proc_ywytc   @proid varchar(50)   as   SELECT * from V_skhz_ywy  where  proid='20080100'     SELECT * from ywytc   */   declare @ywy varchar(50)   declare @zwf varchar(50)   declare @hwf varchar(50)   declare @ggf varchar(50)   declare @cdggf varchar(50)   declare @xcggf varchar(50)   declare @qtfy varchar(50)   declare @qsje decimal(18,2)   declare @zzje decimal(18,2)   declare @tcbl decimal(18,2)   declare @tc decimal(18,2)   declare @tce decimal(18,2)   declare @tcs decimal(18,2)   declare @tcsi decimal(18,2)   declare @tcw decimal(18,2)   declare @tcl decimal(18,2)   declare @ibcount int   declare @iecount int   --创建临时表   两个字段   create table #ywytc          (业务人员 varchar(10),展位费 decimal(18,2),提成一  decimal(18,2),会务费  decimal(18,2),提成二  decimal(18,2), 会刊广告费  decimal(18,2),提成三  decimal(18,2), 场地广告费  decimal(18,2),提成四  decimal(18,2),宣传广告费  decimal(18,2),提成五  decimal(18,2),其他费用  decimal(18,2),提成六  decimal(18,2),总提成额  decimal(18,2))     declare ff cursor for  SELECT ywy,zwf,hwf,ggf,cdggf,xcggf,qtfy from V_skhz_ywy where  proid=@proid   set @ibcount=0   select @iecount=count(*) from V_skhz_ywy where  proid=@proid   open ff   while @ibcount<=@iecount-1   begin     fetch next from ff     into @ywy,@zwf,@hwf,@ggf,@cdggf,@xcggf,@qtfy     /*         SELECT * from ywytc where 费用类型编号='zwf'     SELECT * from ywytc where 费用类型编号='hwf'     SELECT * from ywytc where 费用类型编号='ggf'     SELECT * from ywytc where 费用类型编号='cdggf'     SELECT * from ywytc where 费用类型编号='xcggf'     SELECT * from ywytc where 费用类型编号='qtfy'     */       declare tc cursor for SELECT 起始金额,终止金额,提成比例 from ywytc where 展会项目编号=@proid and 费用类型编号='zwf'       open  tc       fetch next from tc into @qsje,@zzje,@tcbl         while  @@FETCH_STATUS = 0         begin          if @zwf>=@qsje and @zwf<=@zzje           set @tc=@zwf*@tcbl/100            fetch next from tc into @qsje,@zzje,@tcbl               end       close tc       deallocate tc       declare tc cursor for SELECT 起始金额,终止金额,提成比例 from ywytc where 展会项目编号=@proid and 费用类型编号='hwf'       open  tc       fetch next from tc into @qsje,@zzje,@tcbl        while  @@FETCH_STATUS = 0         begin          if @hwf>=@qsje and @hwf<=@zzje           set @tce=@hwf*@tcbl/100            fetch next from tc into @qsje,@zzje,@tcbl               end       close tc       deallocate tc       declare tc cursor for SELECT 起始金额,终止金额,提成比例 from ywytc where 展会项目编号=@proid and 费用类型编号='ggf'       open  tc       fetch next from tc into @qsje,@zzje,@tcbl        while  @@FETCH_STATUS = 0        begin         if @ggf>=@qsje and @ggf<=@zzje          set @tcs=@ggf*@tcbl/100           fetch next from tc into @qsje,@zzje,@tcbl              end       close tc       deallocate tc       declare tc cursor for SELECT 起始金额,终止金额,提成比例 from ywytc where 展会项目编号=@proid and 费用类型编号='cdggf'       open  tc       fetch next from tc into @qsje,@zzje,@tcbl         while  @@FETCH_STATUS = 0         begin          if @cdggf>=@qsje and @cdggf<=@zzje           set @tcsi=@cdggf*@tcbl/100            fetch next from tc into @qsje,@zzje,@tcbl               end       close tc       deallocate tc       declare tc cursor for SELECT 起始金额,终止金额,提成比例 from ywytc where 展会项目编号=@proid and 费用类型编号='xcggf'       open  tc       fetch next from tc into @qsje,@zzje,@tcbl         while  @@FETCH_STATUS = 0         begin          if @xcggf>=@qsje and @xcggf<=@zzje            set @tcw=@xcggf*@tcbl/100          fetch next from tc into @qsje,@zzje,@tcbl               end       close tc       deallocate tc         declare tc cursor for SELECT 起始金额,终止金额,提成比例 from ywytc where 展会项目编号=@proid and 费用类型编号='qtfy'       open  tc       fetch next from tc into @qsje,@zzje,@tcbl         while  @@FETCH_STATUS = 0         begin          if @qtfy>=@qsje and @qtfy<=@zzje           set @tcl=@qtfy*@tcbl/100            fetch next from tc into @qsje,@zzje,@tcbl               end       close tc       deallocate tc   insert #ywytc     select @ywy,@zwf,@tc,@hwf,@tce,@ggf,@tcs,@cdggf,@tcsi,@xcggf,@tcw,@qtfy,@tcl,@tc+@tce+@tcs+@tcsi+@tcw+@tcl     --select @ywy 业务人员,@zwf 展位费,@tc 提成,@hwf 会务费,@tce 提成,@ggf 会刊广告费,@tcs 提成,@cdggf 场地广告费,@tcsi 提成,@xcggf 宣传广告费,@tcw 提成,@qtfy 其他费用,@tcl 提成,@tc+@tce+@tcs+@tcsi+@tcw+@tcl 总提成额     set @ibcount=@ibcount+1   end   close ff   deallocate ff   select * from #ywytc     /*   DECLARE @RC int DECLARE @proid varchar(50) SELECT @proid = '20080100' EXEC @RC = [dbo].[proc_ywytc] @proid DECLARE @PrnLine nvarchar(4000) PRINT '存储过程: dbo.proc_ywytc' SELECT @PrnLine = ' 返回代码 = ' + CONVERT(nvarchar, @RC) PRINT @PrnLine    */ GO @proid 是项目编号 我写了一个存储过程，完成了，大家看看还有没有更好的办法。

zyt_1978 · 2008-09-28

嗯，还是 kaida 厉害呀 原来技巧在 on 里面我写的太笨了。 conlin同学还要努力呀，呵呵，人家kaida一看就很明白。

conlin · 2008-09-28

晕，之前没有反应过来，业务太不熟悉拉，^_^

比较难的SQL语句，看来只能用存储过程了。(50分)

zyt_1978

Unregistered / Unconfirmed

conlin

Unregistered / Unconfirmed

kaida

Unregistered / Unconfirmed

zyt_1978

Unregistered / Unconfirmed

zyt_1978

Unregistered / Unconfirmed

conlin

Unregistered / Unconfirmed

Similar threads