SQLÖÐÈçºÎÓÃÒ»¸ö±í¸üÐÂÁíÒ»¸ö±í
for ACCESS :
update a, b set a.name=b.name1 where a.id=b.id
for SQL Server:
"update a set a.name=b.name1 from a,b where a.id=b.id"
update a set a.status=b.status
from table1 a,table2 b
where a.id1=b.id1
update a inner join b on a.a1=b.b1 set a.a2=b.b2Ìõ¼þ
update table1 set a.status = b.status
from table1 a inner join table2 b
on a.idl = b.idl
http://topic.csdn.net/t/20041013/14/3451961.html
http://www.cnblogs.com/hanguoji/archive/2007/02/01/636723.aspx
Ïà¹ØÎĵµ£º
/*
±êÌ⣺ÆÕͨÐÐÁÐת»»(version 2.0)
×÷Õߣº°®Ð¾õÂÞ.ع»ª
ʱ¼ä£º2008-03-09
µØµã£º¹ã¶«ÉîÛÚ
˵Ã÷£ºÆÕͨÐÐÁÐת»»(version 1.0)½öÕë¶Ôsql server 2000Ìṩ¾²Ì¬ºÍ¶¯Ì¬Ð´·¨£¬version 2.0Ôö¼Ósql server 2005µÄÓйØÐ´·¨¡£
ÎÊÌ⣺¼ÙÉèÓÐÕÅѧÉú³É¼¨±í(tb)ÈçÏÂ:
ÐÕÃû ¿Î³Ì ·ÖÊý
ÕÅÈý ÓïÎÄ 74
ÕÅÈý Êýѧ 83
ÕÅÈý ÎïÀí 93 ......
1ÓÃÓÚÅÅÐòµÄº¯Êý
row_number()
rank()
dense_rank()
ntile(group_number)
ÏÂÃæÁоÙÕâ¸öº¯ÊýµÄÓ÷¨£º
row_number()º¯ÊýÒ»°ãÓÃÓÚ×éÄÚÅÅÐò£¬¶øÆäËûÈý¸öº¯ÊýÊǶԽá¹û¼¯ÅÅÐò
Àý×Ó£º·ÖÒ³ÅÅÐò
<!--×¢ÒâÈ«¾Ö±äÁ¿Ò²ÔÚÕâÀïÉùÃ÷£¬²¢ÓöººÅ¸ô¿ª-->
create proc MyDividePageSort @iRowCount int ,@iPageNo int
AS
< ......
TABLE MASTER ×Ö¶Î ID DETAIL.....
TABLE BIZ ×Ö¶Î SYS_ID CODE_ID .......
²éѯʱÐèÒªµÄÊÇIDµÄÃèÊö
1,SELECT A.SYS_ID,A.CODE_ID,B.DETAIL,C.DETAIL...... from BIZ A,MASTER B,MASTER C WHERE A.SYS_ID=B.ID AND A.CODE_ID=C.ID
2,SELECT SYS_ID,(SELECT DETAIL from MASTER ......
----start
¶¯Ì¬SQLÊÇÔÚ³ÌÐòÔËÐÐʱ¹¹ÔìµÄ£¬ÒªÖ´Ðе¥ÌõSQL£¬Ê¹ÓÃEXECUTE IMMEDATE Óï¾ä£»µ±ÅúÁ¿Ö´ÐÐSQLʱ£¬ÏÈʹÓÃPREPARE Óï¾ä¹¹ÔìSQL£¬È»ºóʹÓÃEXECUTE Óï¾äÖ´ÐС£
Ò»£ºPrepareÓï¾ä£ºÓÃÀ´¹¹ÔìÅúÁ¿SQL
Óï·¨£º
PREPARE <sql-statement> [OUTPUT] INTO <result> [INPUT INTO] <input> ......
ÆÕͨÐÐÁÐת»»
ÎÊÌ⣺¼ÙÉèÓÐÕÅѧÉú³É¼¨±í(tb)ÈçÏÂ:
ÐÕÃû ¿Î³Ì ·ÖÊý
ÕÅÈý ÓïÎÄ 74
ÕÅÈý Êýѧ 83
ÕÅÈý ÎïÀí 93
ÀîËÄ ÓïÎÄ 74
ÀîËÄ Êýѧ 84
ÀîËÄ ÎïÀí 94
Ïë±ä³É(µÃµ½ÈçϽá¹û)£º
ÐÕÃû ÓïÎÄ Êýѧ ÎïÀí
---- ---- ---- ----
ÀîËÄ 74 84 94
ÕÅÈý 74 83 93
-------------------
*/
create table tb(Ð ......