[µo°Ý] VBA SQL ¨ú±o®É¶¡¶¡¹j ªº¨ä¥LÄæ¦ì¤§¥­§¡­È

Scott090

½Ð«üÂI°g¬z¡G

¤U­±ªº SQL ¦r¦ê µ²ºcµLªk±o¨ì¥Øªº¡F ¸ê®Æ¦pªþ¥ó(­ìÀɮ׸ê®Æ¬Æ¤j¡A¥uºI¨ú¤Ö¶q)

1. Option Explicit
   
2. 
   
3. '¥Øªº¡G ¨C¶¡¹j 10 ¤ÀÄÁ¡B¨C¤@­Ó CTcode °µ Äæ¦ì Volts,Hz ªº¥­§¡­È¡A
   
4. '       ¨Ã¨Ì¤w¶¡¹j¤§®É¶¡§Ç¤ÎCTcode §Ç§e²{µ²ªG
   
5. 
   
6. Sub ExcelSQL()
   
7.     Dim SQL As String, TimeIntervalStr$
   
8.     Dim j%, r%
   
9.     Dim cnn As ADODB.Connection
   
10.     Dim rs As ADODB.Recordset
    
11.     Dim ws As Worksheet
    
12.     Dim StartTime#
    
13.     Set ws = Sheets("Temp")
    
14.    
    
15.     StartTime = Timer
    
16.         Set cnn = New ADODB.Connection
    
17.         With cnn
    
18.             .Provider = "Microsoft.ACE.OLEDB.12.0"
    
19.             .ConnectionString = "Extended Properties= Excel 12.0;" _
    
20.                 & "Data Source=" & ThisWorkbook.FullName
    
21.             .Open
    
22.         End With
    
23.         Set rs = New ADODB.Recordset
    
24. 
    
25. ''        SQL = " select * from [CT$] where [¤é´Á®É¶¡] between #2015/6/1# and #2015/6/2#"
    
26.         
    
27.         TimeIntervalStr = " DateAdd(" + """n""" + ",10,[¤é´Á®É¶¡] )"        '¨C¶¡¹j10¤ÀÄÁ
    
28.         
    
29.         SQL = "SELECT" + TimeIntervalStr + " as [DateTime],avg(Volts) as avgVolt,AVg(Hz) as avgHz " & _
    
30.             "from [CT$] " & _
    
31.             "where [¤é´Á®É¶¡] between #2015/06/01# and #2015/06/02# " & _
    
32.             "Group by [DateTime],CTcode " & _
    
33.             "Order by [DateTime]"
    
34.         
    
35.         Debug.Print SQL
    
36.         rs.Open SQL, cnn
    
37.         
    
38.     With ws
    
39.         .Cells.Clear
    
40.         For j = 0 To rs.Fields.Count - 1
    
41.             .Cells(1, j + 1) = rs.Fields(j).Name
    
42.         Next
    
43.         r = .Cells(.Rows.Count, 1).End(xlUp).Row
    
44.         .Range("A" & r + 1).CopyFromRecordset rs
    
45.     End With
    
46.     rs.Close
    
47.     cnn.Close
    
48.     Set rs = Nothing
    
49.     Set cnn = Nothing
    
50.     Debug.Print Timer - StartTime
    
51. End Sub

½Æ»s¥N½X

CT.rar (431.9 KB)

¤U¸ü¦¸¼Æ: 11

2015-7-26 08:11

Scott090

¦^´_ 8# GBKEE


    ¥H¤@­ÓSQL±Ô­z§¹¦¨»Ý¨D²×©ó¥i¥H¹F¦¨¦p¤U¡A¨Ñ°Ñ¦Ò¡F ÁÂÁÂ

1. Sub ExcelSQL2()
   
2. 
   
3.     Dim i&, j%
   
4.     Dim cnn As ADODB.Connection
   
5.     Dim rs As ADODB.Recordset
   
6.     Dim SQL As String, FileName$
   
7.     Dim ws1 As Worksheet
   
8.     Dim ws2 As Worksheet
   
9.     Dim TimeInterval#
   
10.     Dim Time0#
    
11.     Dim StartTime, EndTime, TimeStr$
    
12.    
    
13.     Set ws1 = Sheets("CT")
    
14.     Set ws2 = Sheets("Temp")
    
15.     ws2.Cells.Clear
    
16.    
    
17.     FileName = ThisWorkbook.FullName
    
18.    
    
19.     ws1.Select
    
20. '    StartTime = #1/1/2015 12:00:01 AM#
    
21. '    EndTime = #12/31/2030#
    
22. '    StartTime = CDbl(StartTime)
    
23. '    EndTime = CDbl(EndTime)
    
24.     StartTime = "2015/01/01 00:00:01"
    
25.    
    
26.     TimeInterval = 10   '¶¡¹j 10 ¤ÀÄÁ
    
27.    
    
28. '    Time0 = Timer
    
29.     TimeStr = " format( iif(MINUTE([¤é´Á®É¶¡]) mod " & TimeInterval & " = 0,[¤é´Á®É¶¡], " & _
    
30.                 "DateAdd(""n"", " & TimeInterval & "-MINUTE([¤é´Á®É¶¡]) mod " & TimeInterval & _
    
31.                 " ,[¤é´Á®É¶¡])),""yyyy/mm/dd hh:mm"") "
    
32.                
    
33. '=== ³B²z¼Æ­ÈÄæ¦ìªº¨C 10¤ÀÄÁ¥­§¡ ===========================
    
34.     Set cnn = New ADODB.Connection
    
35.         cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
    
36.         cnn.ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"""
    
37.         cnn.Open
    
38.    
    
39.       SQL = "SELECT " + TimeStr + " as [DateTime], CTcode, " & _
    
40.                 "AVG([Volts]) as [avgVolt], AVG([Hz]) as [avgHz] " & _
    
41.         " From [" & ws1.Name & "$] " & _
    
42.         " Where [¤é´Á®É¶¡] >= #" & StartTime & "# " & _
    
43.         " Group by " + TimeStr + " ,CTcode " & _
    
44.         " Order by " + TimeStr + " ,CTcode"
    
45.         
    
46.     Set rs = cnn.Execute(SQL)
    
47.    
    
48.     With ws2
    
49.         For i = 0 To rs.Fields.Count - 1
    
50.             .Cells(1, i + 1) = rs.Fields(i).Name
    
51.         Next
    
52.         .Cells(2, 1).CopyFromRecordset rs
    
53.     End With
    
54.       
    
55.     rs.Close
    
56.     cnn.Close
    
57.     Set rs = Nothing
    
58.     Set cnn = Nothing
    
59. '    Debug.Print Timer - Time0
    
60. End Sub

½Æ»s¥N½X

Scott090

¦^´_ 10# GBKEE

ÁÂÁ¶Wª©
¥Î§A«ü¾Éªº¤èªk¤£¥Î ThisworkBook.fullname¡A¶]¤@¦¸¬O¬ù 11¬í§Ö¤F³\¦h

¥t¥~½Ð±Ð¡AExcel ¸ê®Æ®w­n·s¼W¤@Äæ¦ì¡A¥Î¤F Alter Table [CT$] add NewColumn char(10) ±o¨ì¹BºâµL®Äªº«H®§
ADO ¤£¤ä´© Alter TABLE ¶Ü?
¤£¾å±o°ÝÃD¦b­þ¸Ì¡A·q½Ð«ü¾É

1. Sub ExcelAddColumn()
   
2.     Dim SQL As String
   
3.     Dim j%, r%
   
4.     Dim cnn As ADODB.Connection
   
5.     Dim rs As ADODB.Recordset
   
6.     Dim ws As Worksheet
   
7.    
   
8.     Time0 = Timer
   
9.     Set ws = ThisWorkbook.Sheets(1)
   
10.     Set cnn = New ADODB.Connection
    
11.    
    
12.     With cnn
    
13.             .Provider = "Microsoft.ACE.OLEDB.12.0"
    
14.               .ConnectionString = "Extended Properties= Excel 12.0;" _
    
15.                 & "Data Source=D:\VBA SQL.xlsm"
    
16.         '«Øij¤£­n Source=" & ThisWorkbook.FullName ¦b³o¸ê®Æ®w¬¡­¶Ã¯¤¤°õ¦æ¦¹¥¨¶°,«Ü¯Ó°O¾ÐÅé
    
17.         '**********************************************************
    
18. '        '2003ª© ¤Þ¥Î¡Gmicrosoft activex data objects 2.x library
    
19. '        .Provider = "microsoft.jet.oledb.4.0"
    
20. '        .ConnectionString = "Extended Properties= Excel 8.0;" _
    
21. '        & "Data Source=D:\VBA SQL.xls"
    
22. '        '«Øij¤£­n Source=" & ThisWorkbook.FullName ¦b³o¸ê®Æ®w¬¡­¶Ã¯¤¤°õ¦æ¦¹¥¨¶°,«Ü¯Ó°O¾ÐÅé
    
23.         .Open
    
24.     End With
    
25.    
    
26.     SQL = "Alter Table [CT$] ADD NewColumn char(10)"   '­n¼W¥[¤@Äæ¦ì¦WºÙ
    
27.    
    
28. '' ¥X²{"µL®Äªº¹Bºâ" ¿ù»~
    
29.     cnn.Execute SQL
    
30.    
    
31.     Set rs = New ADODB.Recordset
    
32.    
    
33.     SQL = "select * from [CT$] "
    
34.     rs.Open SQL, cnn
    
35.     Debug.Print rs.Fields.Count
    
36.    
    
37. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 9# Scott090
¦pThisWorkbook¬O¸ê®Æ®w,¥ú¬O¸ü¤J´N«Ü¦û°O¾ÐÅé.

Scott090

¦^´_ 8# GBKEE

ÁÂÁ¶Wª©ªº«ü¾É
¸Õ°õ¦æªº®É¶¡¬ù 40¬í
¾Ç²ß¨ì¡G
1.¤é´Á®É¶¡¥Î Double³B²z
2.¨C¤@®É¶¡¶¡®æ°µ¤@¦¸ªº SQL ¬d¸ß

¦³¤@ÂI½Ð±Ð
    "'«Øij¤£­n Source=" & ThisWorkbook.FullName ¦b³o¸ê®Æ®w¬¡­¶Ã¯¤¤°õ¦æ¦¹¥¨¶°,«Ü¯Ó°O¾ÐÅé"
¥Î¬Æ»ò¤èªk¯à´î¤Ö°O¾ÐÅ骺¨Ï¥Î?
½Ð©ú¥Ü«ü¾É

GBKEE

¦^´_ 7# Scott090
§Ú¨Ï¥Î2003ª©

1. Option Explicit
   
2. Sub ExcelSQL()
   
3.     Dim SQL As String, TimeIntervalStr As Double
   
4.     Dim j%, r%
   
5.     Dim cnn As ADODB.Connection
   
6.     Dim rs As ADODB.Recordset
   
7.     Dim ws As Worksheet
   
8.     Set ws = ThisWorkbook.Sheets(1)
   
9.     Set cnn = New ADODB.Connection
   
10.     With cnn
    
11.             '.Provider = "Microsoft.ACE.OLEDB.12.0"
    
12.           '    .ConnectionString = "Extended Properties= Excel 12.0;" _
    
13.                 & "Data Source=" & ThisWorkbook.FullName
    
14.         '«Øij¤£­n Source=" & ThisWorkbook.FullName ¦b³o¸ê®Æ®w¬¡­¶Ã¯¤¤°õ¦æ¦¹¥¨¶°,«Ü¯Ó°O¾ÐÅé
    
15.         '**********************************************************
    
16.         '2003ª© ¤Þ¥Î¡Gmicrosoft activex data objects 2.x library
    
17.         .Provider = "microsoft.jet.oledb.4.0"
    
18.         .ConnectionString = "Extended Properties= Excel 8.0;" _
    
19.         & "Data Source=D:\VBA SQL.xls"
    
20.         '«Øij¤£­n Source=" & ThisWorkbook.FullName ¦b³o¸ê®Æ®w¬¡­¶Ã¯¤¤°õ¦æ¦¹¥¨¶°,«Ü¯Ó°O¾ÐÅé
    
21.         .Open
    
22.     End With
    
23.     Set rs = New ADODB.Recordset
    
24.     SQL = "select * from [ct$] "
    
25.     rs.Open SQL, cnn
    
26.     With ws
    
27.         .[A1].CopyFromRecordset rs
    
28.          TimeIntervalStr = .[A1]   '¨ú±o²Ä¤@­Ó®É¶¡
    
29.         .UsedRange.Clear
    
30.     End With
    
31.     Do
    
32.         Set rs = New ADODB.Recordset
    
33.         SQL = "SELECT CTcode, avg(test.Volts) as Volts¥­§¡,avg(test.Hz) as Hz¥­§¡ from [CT$] " & _
    
34.             " as test where ¤é´Á®É¶¡ >=" & TimeIntervalStr & " AND ¤é´Á®É¶¡ <" & CDbl(DateAdd("n", 10, TimeIntervalStr)) & "  Group by CTcode "
    
35.         rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
    
36.         If rs.RecordCount Then   'Ū¨ú¬ö¿ý
    
37.             With ws
    
38.                 If .UsedRange.Count = 1 Then
    
39.                     .Cells(1) = "¤é´Á ®É¶¡"
    
40.                     For j = 0 To rs.Fields.Count - 1
    
41.                         .Cells(1, j + 2) = rs.Fields(j).Name
    
42.                     Next
    
43.                 End If
    
44.                 r = .Cells(.Rows.Count, 2).End(xlUp).Row
    
45.                 .Range("B" & r + 1).CopyFromRecordset rs
    
46.                 r = .Cells(.Rows.Count, 2).End(xlUp).Row
    
47.                 With .Range("A" & Rows.Count).End(xlUp).Offset(1)
    
48.                     .Resize(r - .Row + 1) = DateAdd("n", 0, TimeIntervalStr) & vbLf & DateAdd("n", 10, TimeIntervalStr)
    
49.                 End With
    
50.             End With
    
51.         End If
    
52.         TimeIntervalStr = CDbl(DateAdd("n", 10, TimeIntervalStr)) '¤U¤@­Ó10¤ÀÄÁ
    
53.     Loop Until rs.RecordCount = 0  'µL¬ö¿ý
    
54.     rs.Close
    
55.     cnn.Close
    
56.     Set rs = Nothing
    
57.     Set cnn = Nothing
    
58. End Sub

½Æ»s¥N½X

Scott090

¦^´_ 5# GBKEE


    ¦p¤Uªº ADO recordset ¤è¦¡¥i¥H¹F¨ì¥Øªº¡G
1.¥ý³B²z®É¶¡Äæ¦ì¨Ï¨ä¥i¦¨¬°¤À²Õªº·sÄæ¦ì
2.¥H®É¶¡²Õ³B²z¤À²Õªº¼Æ­ÈÄæ¦ì¥­§¡­È
¦ýÁÙ¬O´Á±æ¥Î¤@­Ó SQL ¦r¦ê§¹¦¨¥Øªº

1. '¥Øªº¡G ¨C¶¡¹j 10 ¤ÀÄÁ¡B¨C¤@­Ó CTcode °µ Äæ¦ì Volts,Hz ªº¥­§¡­È¡A
   
2. '       ¨Ã¨Ì¤w¶¡¹j¤§®É¶¡§Ç¤ÎCTcode §Ç§e²{µ²ªG
   
3. Option Explicit
   
4. Option Base 1
   
5. Option Private Module
   
6. 
   
7. 'Definitions for ADODB SQL
   
8.     Public cnn As ADODB.Connection
   
9.     Public rs As ADODB.Recordset
   
10.     Public SQL As String, FileName$
    
11.     Public ws1 As Worksheet
    
12.     Public ws2 As Worksheet
    
13.     Public TimeInterval#
    
14.     Public Time0#
    
15.     Public StartTime$, EndTime$, TimeStr$
    
16. 
    
17. 
    
18. Sub ExcelSQL()
    
19. 
    
20.     Dim i&, j%
    
21.     Set ws1 = Sheets("CT")
    
22.     Set ws2 = Sheets("Temp")
    
23.     ws2.Cells.Clear
    
24.    
    
25.     FileName = ThisWorkbook.FullName
    
26.    
    
27.     ws1.Select
    
28.     StartTime = Format(Cells(2, 1), "yyyy/mm/dd hh:mm")
    
29.     EndTime = Format(Cells([A1].CurrentRegion.Rows.Count, 1), "yyyy/mm/dd hh:mm")
    
30.     TimeInterval = 10   '¶¡¹j 10 ¤ÀÄÁ
    
31.    
    
32.     Time0 = Timer
    
33.    
    
34. '=== ½á¤©®É¶¡¶¡®æªº·sÄæ¦ì =============
    
35.     Set cnn = New ADODB.Connection
    
36.         cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
    
37.         cnn.ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"""
    
38.         cnn.Open
    
39.             
    
40.     SQL = "Select format( iif(minute([¤é´Á®É¶¡]) mod " & TimeInterval & " = 0,[¤é´Á®É¶¡], " & _
    
41.                 "DateAdd(""n"", " & TimeInterval & "-minute([¤é´Á®É¶¡]) mod " & TimeInterval & _
    
42.                 " ,[¤é´Á®É¶¡])),""yyyy/mm/dd HH:mm"") " & _
    
43.     " From [" & ws1.Name & "$]" & _
    
44.     " where [¤é´Á®É¶¡] >=  #" & StartTime & "#  " & _
    
45.     " order by [¤é´Á®É¶¡]"
    
46.    
    
47.     Set rs = cnn.Execute(SQL)
    
48.    
    
49.     With ws1
    
50.         j = .[A1].End(xlToRight).Column
    
51.         .Cells(1, j + 1) = "DateTime1"
    
52.         .Cells(2, j + 1).CopyFromRecordset rs
    
53.     End With
    
54.     rs.Close
    
55.     cnn.Close
    
56.     Set rs = Nothing
    
57.     Set cnn = Nothing
    
58. 
    
59. '=== ³B²z¼Æ­ÈÄæ¦ìªº¥­§¡ ===========================
    
60.     Set cnn = New ADODB.Connection
    
61.         cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
    
62.         cnn.ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"""
    
63.         cnn.Open
    
64.     SQL = "SELECT format([DateTime1],""yyyy/mm/dd HH:mm"") as [DateTime], CTCode, " & _
    
65.                 "AVG([Volts]) as [avgVolt], AVG([Hz]) as [avgHz], AVG([kW]) as [avgkW] " & _
    
66.         " From [" & ws1.Name & "$] " & _
    
67.         " Where [¤é´Á®É¶¡] >=  #" & StartTime & "# and [DateTime1] <= #" & EndTime & "# " & _
    
68.         " Group by [DateTime1],CTCode " & _
    
69.         " Order by [DateTime1] ,CTCode"
    
70.     Debug.Print SQL
    
71.    
    
72.     Set rs = cnn.Execute(SQL)
    
73.    
    
74.     With ws2
    
75.         For i = 0 To rs.Fields.Count - 1
    
76.             .Cells(1, i + 1) = rs.Fields(i).Name
    
77.         Next
    
78.         .Cells(2, 1).CopyFromRecordset rs
    
79.     End With
    
80.    
    
81.     ws1.Select
    
82.     Cells(1, ws1.UsedRange.Columns.Count).EntireColumn.Clear
    
83.    
    
84.     rs.Close
    
85.     cnn.Close
    
86.     Set rs = Nothing
    
87.     Set cnn = Nothing
    
88. '    Debug.Print Timer - Time0
    
89. End Sub

½Æ»s¥N½X

Scott090

¦^´_ 5# GBKEE


    ÁÂÁ¤j®v¼ö¤ß¦aÄ~Äò«ü¤Þ

Ä~Äò¬ã¨s¤¤¡A¦³¥i¯àªº¤è¦V¬O¥ý¥Î¤@ SQL ³B²z [¤é´Á®É¶¡]¤À§O¦¨ "10"¤ÀÄÁ¶¡¹jªº²Õ¡A
¦A¥Î¥t¤@SQL ¾ã²z¥X¨ä¥Lªº¥­§¡­È

¦³µ²½×¦A³ø§i¤j®v

GBKEE

¦^´_ 4# Scott090

°Ñ¦Ò¤@¤U
    http://www.1keydata.com/tw/sql/sqlalias.html

Scott090

¦^´_ 3# GBKEE

·PÁ«ü¤Þ
±q 1# ªºµ{¦¡½X :
            SQL = " select * from [CT$] where [¤é´Á®É¶¡] between #2015/6/1# and #2015/6/2#"
ªº¹êÅç¡A°ò¥»ªºADO³sµ²³]©w¨S¦³°ÝÃD¡F
°ÝÃD¬O  '¨C¶¡¹j10¤ÀÄÁ­n¨ú¨ä¨ä¥LÄæ¦ìªº¥­§¡­È ³o¼Ëªº ¸ê®ÆÂ^¨ú ªº SQL ¦r¦êµ²ºc­n¦p¦ó°µ¡A¤~¯à¹F¨ì¥Øªº¡C
¥H¤U¬O¦b 1# ¤ºªº°ÝÃD©Ò¦b¡C
·Q¹L ¥Î¤l¬d¸ß¡A¦ý¥¼¯àºc«ä¨äÅÞ¿è¡C
        TimeIntervalStr = " DateAdd(" + """n""" + ",10,[¤é´Á®É¶¡] )"        '¨C¶¡¹j10¤ÀÄÁ        
        SQL = "SELECT" + TimeIntervalStr + " as [DateTime],avg(Volts) as avgVolt,AVg(Hz) as avgHz " & _
            "from [CT$] " & _
            "where [¤é´Á®É¶¡] between #2015/06/01# and #2015/06/02# " & _
            "Group by [DateTime],CTcode " & _
            "Order by [DateTime]"

©|½ÐÄ~Äò¨ó§U