1、4 2、update TestA a set a.Grade = (select b.Grade from TestB b where b.STU_ID = a.STU_ID ) 3、update TestA a set a.STU_ID = a.STU_ID || a.Grade where a.STU_Name like '张%'4、select * from (select a.*,rownum rn from TestA a desc) where rn > 20 and rn<=40没测试 你自己测试看看吧
update TestA a set a.Grade = (select b.Grade from TestB b where b.STU_ID = a.STU_ID ) 这句明显错了 会把TestA里面有的数据会设置为null了,这个正确的做法应该是在循环里面去弄 应该要用存储过程吧?
1).4 2).(这个可以在Oracle下执行 mysql 应该执行不了) begin for p in (select * from TestB)loop update TsetA a set a.Grade=p.Grade where a.STU_ID=p.STU_ID; end loop; end;3).update TestA a set SUT_ID=a.STU_ID||'_'||a.Grade where a.STU_NAME like '张%'; 4).select * from TestA a order by a.Grade desc limit(21,20); (这个只适用mysql)
merge into TestA a using TestB b on (a.id=b.id) when matched then update set a.grade=b.grade; 在Oracle中用merge函数不错
update TestA a set a.Grade = case when (select b.Grade from TestB b where b.STU_ID = a.STU_ID ) = null a.Grade else (select b.Grade from TestB b where b.STU_ID = a.STU_ID ) end;
2、update TestA a set a.Grade = (select b.Grade from TestB b where b.STU_ID = a.STU_ID ) 3、update TestA a set a.STU_ID = a.STU_ID || a.Grade where a.STU_Name like '张%'4、select * from (select a.*,rownum rn from TestA a desc) where rn > 20 and rn<=40没测试 你自己测试看看吧
update TestA a set a.Grade = (select b.Grade from TestB b where b.STU_ID = a.STU_ID ) 这句明显错了 会把TestA里面有的数据会设置为null了,这个正确的做法应该是在循环里面去弄 应该要用存储过程吧?
1).4
2).(这个可以在Oracle下执行 mysql 应该执行不了)
begin for p in (select * from TestB)loop
update TsetA a set a.Grade=p.Grade where a.STU_ID=p.STU_ID;
end loop;
end;3).update TestA a set SUT_ID=a.STU_ID||'_'||a.Grade where a.STU_NAME like '张%';
4).select * from TestA a order by a.Grade desc limit(21,20); (这个只适用mysql)
using TestB b
on (a.id=b.id)
when matched then
update
set a.grade=b.grade;
在Oracle中用merge函数不错