怎样用一张表除第二张表得到第三张表

jason19810924 2011-04-12 10:23:21

MS sql server 2000里..怎么实现啊..

...全文

140 24 打赏收藏转发到动态举报

写回复

用AI写文章

24 条回复

切换为时间正序

请发表友善的回复…

发表回复

jason19810924 2011-04-13

打赏
举报

哎,楼上答得不错.可惜是我问题问错了.





DECLARE mycu2 CURSOR FOR    SELECT   mtl_class,l,w,g,h,oh,isnull(st_qty,0),isnull(zsqty,0)    FROM #t4  order by oh

OPEN mycu2

FETCH NEXT FROM mycu2 INTO @mtl_class ,@l ,@w ,@g ,@h,@oh,@st_qty ,@zsqty 

WHILE @@FETCH_STATUS = 0

BEGIN

     

    if  substring(@mtl_class,3,1)<>'Y' 

    begin

        if substring(@mtl_class,3,1)='D'

        begin

           set @mtl_des = cast(@l as varchar(10))

           if @g <> 6  set @mtl_des =@mtl_des+'x'+cast(@g as varchar(10))

           set @mtl_name ='圓形管'+cast(@l as varchar(10))+'x'+ cast(@g as varchar(10))

       end

       else

       begin

          set @mtl_des = cast(@l as varchar(10)) +'x'+cast(@w as varchar(10))

          if @g <> 6  set @mtl_des =@mtl_des+'x'+cast(@g as varchar(10))

          if substring(@mtl_class,3,1)<>'F' 

            set @mtl_name ='方形管'+cast(@l as varchar(10)) +'x'+ cast(@w as varchar(10))+'x'+cast(@g as varchar(10))

          else

            set @mtl_name ='矩形管'+cast(@l as varchar(10)) +'x'+ cast(@w as varchar(10))+'x'+cast(@g as varchar(10))

        end

    end

    else

    begin

       set @mtl_des = cast(@l as varchar(10))

       if @g <> 6   set @mtl_des =@mtl_des+'x'+cast(@g as varchar(10))

       set @mtl_name ='圓形管'+cast(@l as varchar(10))+'x'+cast(@g as varchar(10))

    end



    if substring(@mtl_class,1,2) ='YF'  

    begin

       if substring(@mtl_class,3,1) ='Y'  

       begin

          set @mtl_name='B0' 

          set @cmtlcode='園B'

       end

       else

       begin

         if substring(@mtl_class,3,1) ='F'  

           set @mtl_name='B1'

         else

         begin

           if substring(@mtl_class,3,1) ='H'  

             set @mtl_name='B3'

           else

           begin

             if substring(@mtl_class,3,1) ='D'                 set @mtl_name='B4'             else               set @mtl_name='B2' 

           end 

         end 

       end 

    end

    else

    begin

      if substring(@mtl_class,1,2) ='YP'  

      begin

         set @mtl_name='A2' 

         set @cmtlcode='矩' 

         if substring(@mtl_class,3,1) ='Y'  

         begin

            set @mtl_name='A0' 

            set @cmtlcode='園' 

         end 

         if substring(@mtl_class,3,1) ='F'  

         BEGIN

            set @mtl_name='A1' 

            set @cmtlcode='方' 

         END 

         if substring(@mtl_class,3,1) ='H'  

         begin

           set @mtl_name='A3' 

           set @cmtlcode='花' 

         end 

         if substring(@mtl_class,3,1) ='D'  

         begin

           set @mtl_name='A4' 

           set @cmtlcode='訂' 

         end 

       end

       else

       begin

         set @mtl_name='C' 

         set @cmtlcode='不' 

       end 

    end



    set @tqty =(  case when @lb=0 then cast(@st_qty as varchar(10))

                                    when @lb=1 then cast(@zsqty as varchar(10))

                                     else cast(@st_qty as varchar(10)) +' / ' +  cast(@zsqty as varchar(10))    end)

                                       



--    select  @mtl_name ,@mtl_des,@stqty,@mtl_class,@zsqty

       

  set @sql=  '   if not exists( select * from '+@USERID  +'  where mtl_class='+char(39)+@mtl_class+char(39)+'  and mtl_des='+char(39)+@mtl_des+char(39)+')'+

                    '   begin  ' +

                    '   insert  ' +@USERID +'  (mtl_class,mtl_des,mtl_name,sn,['+@oh+'],cmtldes) '+

                    '   select  ' + char(39)+@mtl_class+char(39)+','+char(39)+@mtl_des+char(39)+','+char(39)+@mtl_name+char(39)+','+cast(isnull(@l,0) as varchar(10))+','+char(39)+@tqty+char(39)+','+char(39)+@cmtlcode+char(39)+

                    '   end     ' +

                    '   else    ' +

                    '  update  ' + @USERID  +'  set ['+@oh+']  = '+char(39)+@tqty +char(39)+'  where mtl_class='+char(39)+@mtl_class +char(39)+'  and mtl_des= '+char(39)+@mtl_des+char(39)

exec( @sql)

      FETCH NEXT FROM mycu2 INTO @mtl_class ,@l ,@w ,@g ,@h,@oh,@st_qty ,@zsqty 

END

CLOSE mycu2

DEALLOCATE mycu2

EXEC('SELECT * FROM  '+@USERID + ' order by mtl_name,sn,mtl_class,mtl_des')

再帮我看看最后那个游标,是什么意思?

--小F-- 2011-04-12

打赏
举报

select * from a where checksum(*) not in(select checksun(*) from b)

jason19810924 2011-04-12

打赏
举报

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'except'.

快溜 2011-04-12

打赏
举报

你到底想干啥

jason19810924 2011-04-12

打赏
举报

能同步更新吗

快溜 2011-04-12

打赏
举报

select * from 表1

except

select * from 表2

yiyishuitian 2011-04-12

打赏
举报

难道说的是 EXCEPT 联接

jason19810924 2011-04-12

打赏
举报

上面是表2

jason19810924 2011-04-12

打赏
举报

sn Xn M_class mtl_des mtl_name 0.6厚 0.7厚 0.8厚
2 22 YPF 22x22 方形管22x22 0.96 0.95 1.2
3 25 YPJ 25x13 矩形管25x13 0.95 0.98 1.3
3 30 YPJ 30x15 矩形管30x15 0.85 0.89 1.1
1 19 YPY 19 圓形管19 13.2 0.77 0.99
1 22 YPY 22 圓形管22 13.2 0.74 0.79
1 25 YPY 25 圓形管25 NULL 0.94 1.02
1 31.8 YPY 31.8 圓形管31.8 NULL NULL 1.2
2 25 YPF 25x25 方形管25x25 NULL 1.1 1.1
3 38 YPJ 38x25 矩形管38x25 NULL NULL NULL
2 30 YPF 30x30 方形管30x30 NULL NULL NULL
1 38 YPY 38 圓形管38 NULL NULL NULL
1 50.8 YPY 50.8 圓形管50.8 NULL NULL NULL
2 38 YPF 38x38 方形管38x38 NULL NULL NULL
1 63 YPY 63 圓形管63 NULL NULL NULL
3 50 YPJ 50x25 矩形管50x25 NULL NULL NULL

jason19810924 2011-04-12

打赏
举报

表1
sn Xn M_class mtl_des mtl_name 0.6厚 0.7厚 0.8厚
2 22 YPF 22x22 方形管22x22 13.2 13.2 12.7
3 25 YPJ 25x13 矩形管25x13 13.2 13.2 12.7
3 30 YPJ 30x15 矩形管30x15 13.2 13.2 12.7
1 19 YPY 19 圓形管19 13.2 13.2 12.7
1 22 YPY 22 圓形管22 13.2 13.2 12.7
1 25 YPY 25 圓形管25 NULL 13.2 12.7
1 31.8 YPY 31.8 圓形管31.8 NULL NULL 12.7
2 25 YPF 25x25 方形管25x25 NULL 13.2 12.7
3 38 YPJ 38x25 矩形管38x25 NULL NULL NULL
2 30 YPF 30x30 方形管30x30 NULL NULL NULL
1 38 YPY 38 圓形管38 NULL NULL NULL
1 50.8 YPY 50.8 圓形管50.8 NULL NULL NULL
2 38 YPF 38x38 方形管38x38 NULL NULL NULL
1 63 YPY 63 圓形管63 NULL NULL NULL
3 50 YPJ 50x25 矩形管50x25 NULL NULL NULL

AcHerat 2011-04-12

打赏
举报



select a.col1/b.col2 as col3

from a,b

where a.[] = b.[]

investruth 2011-04-12

打赏
举报

[Quote=引用 2 楼 ssp2009 的回复:]
表3=表1/表2
[/Quote]
V5.....

快溜 2011-04-12

打赏
举报

表3=表1/表2

xuam 2011-04-12

打赏
举报

给出具体数据！！！

xuam 2011-04-12

打赏
举报

select a.sn, 

a.Xn, 

a.M_class,

 a.mtl_des, 

a.mtl_name, 

case a.[0.6厚] when null then null else a.[0.6厚]/isnull(b.[0.6厚],1) end as [0.6厚], 

case a.[0.7厚] when null then null else a.[0.7厚]/isnull(b.[0.7厚],1) end as [0.7厚], 

case a.[0.8厚] when null then null else a.[0.8厚]/isnull(b.[0.7厚],1) end as [0.8厚]from 表1 a ,表2 b  where a.sn =b.sn and a.Xn =b.Xn and a.M_class =b.M_class

[Quote=引用 22 楼 jason19810924 的回复:]
怎样使空值还是空值...
[/Quote]

jason19810924 2011-04-12

打赏
举报

怎样使空值还是空值...

xuam 2011-04-12

打赏
举报

select a.sn, 

a.Xn, 

a.M_class,

 a.mtl_des, 

a.mtl_name, 

a.[0.6厚]/isnull(b.[0.6厚],1) as [0.6厚], 

a.[0.7厚]/isnull(b.[0.7厚],1) as [0.7厚], 

a.[0.8厚]/isnull(b.[0.8厚],1) as [0.8厚]

from 表1 a ,表2 b  where a.sn =b.sn and a.Xn =b.Xn and a.M_class =b.M_class

xuam 2011-04-12

打赏
举报

select a.sn, 

a.Xn, 

a.M_class,

 a.mtl_des, 

a.mtl_name 

a.[0.6厚]/b.[0.6厚] as [0.6厚], 

a.[0.7厚]/b.[0.7厚] as [0.7厚], 

a.[0.8厚]/b.[0.8厚] as [0.8厚]

from 表1 a ,表2 b  where a.sn =b.sn and a.Xn =b.Xn