ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

SQL¦hªí®æ¿z¿ï±Ð¾Ç

SQL¦hªí®æ¿z¿ï±Ð¾Ç

¥»©«³Ì«á¥Ñ joey0415 ©ó 2013-11-6 21:37 ½s¿è

¬Ý¨ì¦³«Ü¦h¤H°ÝÃþ¦üªº°ÝÃD¡A¤§«e¤]´¿µo¥Í¦b¦Û¤v¨­¤W¡A¤µ¤Ñ³z¹L¼v¤ù±Ð¾Ç¤è¦¡¥i¯à·|©ú¥Õ¤@¨Ç

±`±`¦³¤H°Ý­n±q³\¦hSHEET¤¤§ä¨ì·Q¨ìªº¸ê®Æ¨Ã¿z¿ï¡A2003¥u¯à¦³¤TºØ±Æ§Ç¡A2010¦n¹³¯à§ó¦h

¦ý¬O¦pªG·|SQL»yªk¡A¥H¤Wªº°ÝÃD³£¤£¬O°ÝÃD¡A¤dÅܸU¤Æ¡A°O±o¤§«e¦bEXCEL HOME¤¤¦³¤H¼g¨ì¡A¼Æ¤Q¸Uµ§¸ê®Æ¤¤§ä¨ì§A­nªºEXCEL¶Ç²Îªº¤èªk¤@¦ý¼Æ¶q¤j¤F¡A®É¶¡·|©Ô«Üªø¡A¦ÓSQLªk¨ÌµM¥u­n¼Æ¬íÄÁ¡A¥[¤W¦Û¤w¿Ë¨­ÅéÅç¹L¡A©Ò¥Hµo²{«Ü­«­n¡A¥u¤£¹LEXCEL¤¤ªº¸ê®Æªí­n¥[¤W [    ]  ¡A¥¿±`ªº¸ê®Æ®w»yªk¬O¤£¥Îªº¡A¦pªG§Aªº¸ê®Æ¨C¤Ñ¦³¼Æ¤Q¸Uµ§ªº¡A¨ä¹ê­n§â¸ê®Æ©ñ¶i¯u¥¿ªº¸ê®Æ®w¤¤¤~¬O³Ì¦nªº¤è¦¡¡A¦pªG¦³°ª¤â¯à«üÂI§ó¦nªº¤èªk¡A½Ð¦h«ü±Ð¡IÁÂÁÂ
SQL»yªk±Ð¾Çºô¯¸

¼v¤ù¤À¨âºØ2003»P2010¸ô®|¤£¦P

¥i¥H¬Ý¼v¤ù§ä¨ì¤èªk©Î¬O¥Î¿ý»sªº­×§ï¦ì¸m§Y¥i¡I
sheet1 ¤º®e

sheet2¤º®e

=========================
¼v¤ù±Ð¾Ç¡G
https://www.youtube.com/watch?v=J4jX1s-ZsOM&feature=youtu.be
2010­«­n¹Ï¤ù







=========================
¼v¤ù±Ð¾Ç¡G
https://www.youtube.com/watch?v=crvrCDQtM60&feature=youtu.be
2003­«­n¹Ï¤ù









=========================
2003 vba»yªk

¬õ¦â¦r³Bªí¥Ü»Ý­n­×§ïªº»yªk
F:\data.xls  §ï¦¨¦Û¤vÀɮתº¸ô®|
select * from [Sheet1$] a left join [Sheet2$] b on a.­q³æ½s¸¹=b.­q³æ½s¸¹      §ï¦¨§A­nªºSQL»yªk
  1. Sub Macro1()
  2. '
  3.     With ActiveSheet.QueryTables.Add(Connection:=Array( _
  4.         "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=F:\data.xls;Mode=Share Deny Write;Extended Properties=""HD" _
  5.         , _
  6.         "R=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet OLED" _
  7.         , _
  8.         "B:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Passwo" _
  9.         , _
  10.         "rd="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet O" _
  11.         , "LEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination _
  12.         :=Range("A1"))
  13.         .CommandType = xlCmdTable
  14.         .CommandText = Array("select * from [Sheet1$] a left join [Sheet2$] b on a.­q³æ½s¸¹=b.­q³æ½s¸¹")
  15.         .Name = "data_3"
  16.         .FieldNames = True
  17.         .RowNumbers = False
  18.         .FillAdjacentFormulas = False
  19.         .PreserveFormatting = True
  20.         .RefreshOnFileOpen = False
  21.         .BackgroundQuery = True
  22.         .RefreshStyle = xlInsertDeleteCells
  23.         .SavePassword = False
  24.         .SaveData = True
  25.         .AdjustColumnWidth = True
  26.         .RefreshPeriod = 0
  27.         .PreserveColumnInfo = True
  28.         .SourceDataFile = "F:\data.xls"
  29.         .Refresh BackgroundQuery:=False
  30.     End With
  31. End Sub
½Æ»s¥N½X
=====================
SQL»yªk½d¨Ò

select * from [Sheet1$] where ­q³æ½s¸¹='A002'
select * from [Sheet1$] where °e³f³Æµù='»Ý¦^¦¬Â§¨÷'
select * from [Sheet1$] where °e³f³Æµù='»Ý¦^¦¬Â§¨÷' and Á`ª÷ÃB=0
select * from [Sheet1$] a left join [Sheet2$] b on a.­q³æ½s¸¹=b.­q³æ½s¸¹
select * from [Sheet1$] a left join [Sheet2$] b on a.­q³æ½s¸¹=b.­q³æ½s¸¹ where a.­q³æ½s¸¹='A002'


=============================

¤å¥ó¤U¸ü³B¡G
SQL.zip (30.3 KB)

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD