Ò»ÌõSQLÓï¾ä£¬¹ØÓÚ×Ö·û·Ö¸î¹ØÁª¶àÌõ¼Ç¼µÄÎÊÌâ
ÔÎÄ´«ËÍÃÅ£ºhttp://topic.csdn.net/u/20091010/14/FC7737C1-D60B-43F1-A8B5-A9EEF2DE4426.html
¼ÙÈçÏÖÔÚÓÐÁ½ÕÅ±í£º
1.±ístuinfo
sid sname subs
1 jack |1|2|
2 marry |1|4|
3 tom |3|
2.±ísubinfo
subid subname
1 physics
2 maths
3 biology
4 geography
ÎÒÏë°ÑstuinfoµÄsubs×ֶΰ´£ü×Ö·û·Ö¿ª£¬È»ºóÆ¥ÅäsubinfoµÄsubid£¬È¡subnameµÄÖµ£¬Æ¥Åäµ½¶à¸öµÄ»°¾ÍÓöººÅ¸ô¿ª¡£
¼ò¶øÑÔÖ®ÎÒÏëµÃµ½µÄ½á¹ûÈçÏ£º
sid sname subname
1 jack physics,maths
2 marry physics,geography
3 tom biology
Ï£Íû¸÷λ´óÏÀÄܰïæÉè¼ÆÒ»ÏÂSQLÓï¾ä£¬Ð»Ð»ÁË£¡
Oracle 10gÒÔÉϰ汾
select a.sid,a.sname,wm_concat(b.subname) subname
from stuinfo a,subinfo b
where instr(a.subs,'|'||b.subid(+)||'|')>0
group by a.sid,a.sname
9iµÄ
select sid,sname,substr(max(sys_connect_by_path(subname,',')),2) subname
from (
select a.sid,a.sname,b.subname,
row_number()over(partition by a.sid order by rownum)rn
from stuinfo a,subinfo b
where instr(a.subs,'|'||b.subid(+)||'|')>0)
connect by prior rn=rn-1 and prior sid=sid
start with rn=1
group by sid,sname
Ïà¹ØÎĵµ£º
SQL SERVER ºÍEXCELµÄÊý¾Ýµ¼Èëµ¼³ö
1¡¢ÔÚSQL SERVERÀï²éѯExcelÊý¾Ý:
-- ======================================================
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
ÏÂÃæÊǸö²éÑ ......
Èç¹ûÒ»¸ö SQL Óï¾ä·¢ÉúÁË´íÎó£¬ÄÇô sqlca.sqlcode ½«ÊÇ·ÇÁãÖµ¡£
Èç¹û sqlca.sqlcode СÓÚ 0 ÄÇô¾ÍÊÇ·¢ÉúÁËijÖÖÑÏÖØµÄ´íÎó£¬ÏóÊý¾Ý¿â¶¨ÒåÓë²éѯ¶¨Òå²»Ò»Öµȣ®
Èç¹û´óÓÚ 0 ÔòÊÇͨ³£µÄ´íÎó£¬Ïó±í²»°üÀ¨ËùÒªÇóµÄÐеȣ®
sqlca.sqlcode == 0£¬³É¹¦
sqlca.sqlcode == -1 ʧ°Ü £¨»ñÈ¡¾ßÌåʧ°ÜÐÅÏ¢ ......
΢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý(clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý)ºÍ·Ç¾Û¼¯Ë÷Òý(nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý)……
¡¡¡¡(Ò»)ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¡¡¡¡Êµ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý(clustered index£¬Ò²³Æ ......
¡¡×î½üΪÊý¾Ý¿â·þÎñÆ÷Ôö¼ÓÁËÄڴ棬´ïµ½ÁË×î´óÖ§³ÖµÄ8G£¬Êý¾Ý¿âÓõÄÊÇmssql 2005 £¬Ö®Ç°ÄÚ´æÒ»Ö±ÊÇ4GµÄ£¬²»´æÔÚÄÚ´æ´óºÍ32λ²Ù×÷ϵͳ³åÍ»µÄÊÂÇ飬32λ²Ù×÷ϵͳµ¥½ø³Ì×î´óÖ§³Ö2GµÄÄڴ棬ÕâÑù×ӵϰÄÚ´æ¾Í°×¼ÓÁË£¬Ôõô°ìÄØ?
¡¡¡¡ÍøÉÏËÑË÷Á˺ܶà×ÊÁÏ£¬·¢ÏÖ΢ÈíÌṩÁËÒ»¸öËãÊÇÁÙʱµÄ½â¾ö·½°¸°É£¬Ê¹ÓÃAWEÀ´·ÖÅäÄڴ棬ÕâÑù×Ósqlse ......