SQL¶ÁÈ¡EXCEL
Ö±½ÓÔÚSQL²éѯ·ÖÎöÆ÷ÖжÁÈ¡EXCELÎļþÐèҪʹÓõ½OPENDATASOURCE¡£
µ«ÊÇʹÓÃËü֮ǰÐèÒª½øÐÐÅäÖÃһϡ£¼ÇµÃÈçÏÂÅäÖÃÊDZØÐëµÄ£º
1¡¢Ö´ÐÐÕâÁ½¸ö´æ´¢¹ý³Ì£º
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
ËüµÄ×÷Óãº
µÚÒ»¸öÊÇ£ºÊÇ·ñÖ§³Ö¸ß¼¶Ñ¡ÏîµÄ£¬1Ϊ֧³Ö0Ϊ²»Ö§³Ö¡£
µÚ¶þ¸öÊÇ£ºÊÇ·ñÖ§³Ö·Ö²¼Ê½²éѯ£¬
1Ϊ֧³Ö0Ϊ²»Ö§³Ö¡£
¶øÇÒÒ»¶¨ÊÇÏÈÖ§³Ö¸ß¼¶Ñ¡ÏÔÙ¿ÉÒÔÉèÖ÷ֲ¼Ê½²éѯ£¬ÒòΪ·Ö²¼Ê½²éѯ±¾Éí¾ÍÊÇ
Ò»¸ö¸ß¼¶Ñ¡ÏîÀ´µÄ¡£
2¡¢Ê¹ÓÃOPENDATASOURCE£¬ËüÓÐÁ½ÖÖÓï·¨
£¨1£©SELECT * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\TempExcelData\Exl_Test_01.xls;Extended Properties=EXCEL 5.0')...[Student$]
£¨2£©select *
from Openrowset('Microsoft.Jet.OLEDB.4.0','EXCEL 8.0;HDR=YES;User id=admin;Password=;IMEX=1;
DATABASE=D:\TempExcelData\Exl_Test_01.xls', Student$)
3¡¢ÓÃÍêºóÒª¹Ø±ÕµÚÒ»²½´ò¿ªµÄ¶«Î÷
exec sp_configure 'show advanced options',0
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
°´Õմ˲½²»Ò»¶¨¿ÉÒÔ²éѯµ½ÄãEXCELµÄÊý¾Ý£¬¿ÉÄÜ»¹»áÓÐÆäËüµÄ´íÎ󣬱ÈÈç˵ȨÏÞ
²»×ã¹»Òý·¢ÆäËüµÄÎÊÌâ°¡£¬ÒªÔÚÍøÉÏÕÒ¶àһϣ¬¾ÍÈçÎÒÅäÖõÄʱºò£¬ÓõÄÊÇSA½øÈ¥µÄ£¬
µ«ÊÇSA²»ÊÇSYSADMINISTRATORÕâÒ»×飬Ҫ¼Ó½øÈ¥ºó²ÅÓÐȨÏÞ£¬²ÅÄÜ˳Àû²éµ½Êý¾Ý
Ïà¹ØÎĵµ£º
sql="select * from (select top 4 ID,SmallPic,NewsNameSi,EndDate,ContentSi,SortID from achi_news where ProductProperty=1 and IsOk=1 and HomeForcePage=1 and HomeEndTime>getDate() and isdate(HomeEndTime)=1 order by HomeorderNum asc )a union all select * from (select top 4 ID,SmallPic,NewsNameS ......
Èç¹ûÏë²éÕÒ“_cs”½áβµÄµÄÕË»§
select * from [user] where loginname like '%_cs'ÊDz»Ðеģ¬_ ±»ÈÏΪÊÇÈÎÒâµÄ×Ö·û£¬
ËùÒÔÐèҪתÒå×Ö·û£¬ÓÐÁ½ÖÖд·¨£º
select * from [user] where loginname like '%[_]cs'
select * from [user] where loginname like '%/_cs' escape'/'
ͨÅä·ûº¬Òå
%
°üº¬Áã¸ö»ò¸ü¶à×Ö· ......
µ÷ÊÔSQLÊý¾Ý£¬·¢ÏÖÊý¾Ý¼Ç¼¼¯Öظ´ÎÊÌ⣬ËùÒÔ£¬¼ÆËã³öµÄÊý¾Ý½á¹û±¶ÊýÎÊÌ⡣ͨ¹ýµ÷ÊÔSQL£¬·¢ÏÖÊÇÎïÁϵķÖÀà²úÉúÖØ¸´£»Ö®ËùÒÔ²úÉúÖØ¸´£¬ÎïÁϵķÖÀà±ê×¼²»Ò»Ñù£¬Óëʵ¼ÊµÄÒµÎñÓйء£³ÌÐòÖÐÒ»Ö±ÓÃÀà±ðÀ´Çø·ÖÀà±ð£¬¶øÕâÕÅ´Îʵ¼ÊÒµÎñ²»ÐèÒªÓëÀà±ðÓйأ¬ËùÒÔ£¬Ã»ÓжÔÓ¦µÄ¹ýÂËÌõ¼þ£¬ËùÓеÄÀà±ðÈ«²¿Ñ¡³öÀ´ÁË¡£È»ºó£¬°ÑÏÂÃæµÄºìÉ«×Ö¶Î×¢ ......
sql Á½±í¹ØÁª ¸üÐÂ
update set from Óï¾ä¸ñʽ
SybaseºÍSQL SERVER£ºUPDATE...SET...from...WHERE...µÄÓï·¨£¬Êµ¼ÊÉÏ´ÓÔ´±í»ñÈ¡¸üÐÂÊý¾Ý¡£
ÔÚ SQL ÖУº
Update A SET A.dept =B.name
from A LEFT JOIN B ON B.ID=A.dept_ID ......
±¾ÎÄ×ªÔØÓÚ£ºhttp://www.javaeye.com/topic/185385
ѧϰÊý¾Ý¿â²éѯµÄʱºò¶Ô¶à±íÁ¬½Ó²éѯµÄÓÐЩ¸ÅÄ±È½ÏÄ£ºý¡£¶øÁ¬½Ó²éѯÊÇÔÚÊý¾Ý¿â²éѯ²Ù×÷µÄʱºò¿Ï¶¨ÒªÓõ½µÄ¡£¶ÔÓڴ˸ÅÄî
ÎÒÓÃͨË×һЩµÄÓïÑÔºÍÀý×ÓÀ´½øÐн²½â¡£Õâ¸öÀý×ÓÊÇÎÒ½²¿ÎµÄʱºò¾³£²ÉÓõÄÀý×Ó¡£
Ê×ÏÈÎÒÃÇ×öÁ½ÕÅ±í£ºÔ±¹¤ÐÅÏ¢±íºÍ²¿ÃÅÐÅÏ¢±í£¬ÔÚ´Ë£¬±íµ ......