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

[µo°Ý] ¨Ì±ø¥ó½Æ»s¤£¦P¤u§@ªí¸ê®Æ¨Ã²Î­p¤£­«½Æ¦¸¼Æ

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2023-3-4 07:00 ½s¿è

·PÁ¦¹©«Åý§Ú½m²ß ·PÁ«e­±¦U¦ì¤j¤j ¤À¨É½m²ßÀÉ®×


    Sub sql§ìÀÉ()
With CreateObject("adodb.connection"): V = Application.Version:
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; "
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; "
.Open V & "Data Source=" & ThisWorkbook.Path & "\" & "Data1.xls"  
For Each Z In Sheets("Á`ªí").Range("a2:a15").Value
If Z <> "" Then T = T & ",'" & Z & "'"
Next: T = Mid(T, 2, 9999)
q = "select a.*,cstr(a.¤é´Á)+a.³¡ªù+a.¦WºÙ+left(®Æ¸¹,7) as NN from  [Sheet1$A5:L] as a where a.³¡ªù in(" & T & ") "
Sheets("tmp").Cells.ClearContents
Sheets("tmp").[A1].Resize(1, 14) = Split("a1,a2,¤é´Á,a4,a5,³¡ªù,a7,¦WºÙ,®Æ¸¹,a10,¼Æ¶q,¦a°Ï,NN,key", ",")
Sheets("tmp").[A2].CopyFromRecordset  .Execute(q)
.Close: .Open V & "Data Source=" & ThisWorkbook.Path & "\" & "Data2.xls"
r = Sheets("tmp").Cells(Rows.Count, 3).End(3).Row
Sheets("tmp").Cells(r + 1, 1).CopyFromRecordset .Execute(q)
.Close: .Open V & "Data Source=" & ThisWorkbook.FullName
Set rs = .Execute("select * from [tmp$A1:N] order by ¤é´Á,¼Æ¶q desc")
Sheets("tmp").Cells(2, 1).CopyFromRecordset rs
r = Sheets("tmp").Cells(Rows.Count, 3).End(3).Row - 1
ReDim ar(1 To r, 0): For i = 1 To r: ar(i, 0) = i: Next
Sheets("tmp").[N2].Resize(r, 1) = ar

q = "select * from ( "
q = q & "SELECT C.a1,C.a2,C.¤é´Á,C.a4,C.a5,C.³¡ªù,C.a7,C.¦WºÙ,C.®Æ¸¹,C.a10,C.¼Æ¶q,C.¦a°Ï FROM ("
q = q & " SELECT NN,MIN(KEY) as K FROM [tmp$A1:N] GROUP BY NN "
q = q & " ) B INNER JOIN [tmp$A1:N] C ON B.K = C.KEY"
q = q & " ) D order by D.¤é´Á,D.¦WºÙ"
Sheets("Á`ªí").Cells(6, 2).Resize(1000, 15).ClearContents
Sheets("Á`ªí").Cells(6, 2).CopyFromRecordset .Execute(q)
End With
End Sub




Sub sql²Î­p¦WºÙ_¦a°Ï_³¡ªù()
With CreateObject("adodb.connection"): V = Application.Version:
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; "
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; "
.Open V & "Data Source=" & ThisWorkbook.FullName
Set s = Sheets("²Î­p")
s.Rows("4:30").ClearContents
q = "select ³¡ªù,¦WºÙ,®Æ¸¹,count(¦WºÙ) as cnt from [Á`ªí$B5:P] group by ¦WºÙ,®Æ¸¹,³¡ªù order by ³¡ªù,¦WºÙ,®Æ¸¹ "
s.Range("C4").CopyFromRecordset .Execute(q)
q = "select ³¡ªù,¦a°Ï,count(³¡ªù) as cnt from [Á`ªí$B5:P] group by ³¡ªù,¦a°Ï order by ³¡ªù,¦a°Ï "
s.Range("J4").CopyFromRecordset .Execute(q)
q = "select ³¡ªù,count(³¡ªù) as cnt from [Á`ªí$B5:P] group by ³¡ªù order by ³¡ªù "
s.Range("P4").CopyFromRecordset .Execute(q)
End With: s.Select
End Sub

T18-2.zip (45.01 KB)

TOP

        ÀR«ä¦Û¦b : °µ¦n¨Æ¤£¯à¤Ö§Ú¤@¤H¡A°µÃa¨Æ¤£¯à¦h§Ú¤@¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD