如何将Group by和Where写在一个SQL中，实现目标结果。

ning84 · 发表于 2024-6-3 11:57

如何将Select Name,Count(Name) From [Sheet1$C1:D18] where Not Name is Null Group by Name
与Select Path From [Sheet1$C1:D18] where Name = 'B'
写在一个SQL中，实现目标结果。

Sub ll()
Dim Str, Str1, SqlStr
Dim Rs As Recordset, Rs1 As Recordset
Dim Rng As Range, AddRng As Range
Set Rng = Selection
Dim Cc
Cc = 8
Dim Sht As Worksheet
Set Sht = Rng.Parent
Debug.Print Sht.Cells(1, 1).Formula
Set AddRng = Sht.Range(Sht.Cells(1, 1).Formula)
Str = "Select Name,Count(Name) From [" & Sht.Name & "$" & AddRng.Address(0, 0) & "] where Not Name is Null Group by Name"
Debug.Print Str
Set Rs = SqlRetuRs(Str)
'
Set Rng = Sht.Cells(3, Cc)
Rng.CopyFromRecordset Rs
''
With Rs
.MoveFirst
If .RecordCount > 1 Then
For ii = 0 To .RecordCount - 1
Str = "Select Path From [" & Sht.Name & "$" & AddRng.Address(0, 0) & "] where Name = '" & .Fields(0) & "'"
Debug.Print Str
Set Rs1 = SqlRetuRs(Str)
''
aa = Rs1.GetRows()
With Application.WorksheetFunction
Sht.Cells(3 + ii, Cc + 4).Resize(, Rs1.RecordCount) = .Transpose(.Transpose(aa))
End With
.MoveNext
Next ii
End If
End With
''
''Stop
End Sub
Function SqlRetuRs(SqlStr)
Dim Cn As ADODB.Connection
Set Cn = New ADODB.Connection
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
'
If InStr(UCase(Application.Path), "WPS") > 0 Then
Cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
Else
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName
End If
Rs.Open SqlStr, Cn, adOpenKeyset, adLockOptimistic
Set SqlRetuRs = Rs
End Function

复制代码

zpy2 · 发表于 2024-6-3 13:52

这种Excel sql比较难，可以试试sqlite
https://club.excelhome.net/forum.php?mod=viewthread&tid=1692500&fromguid=hot&extra=&mobile&_dsign=ae4c00b1

MR_PP · 发表于 2024-6-3 14:08

这个有附件吗

nihao123456789 · 发表于 2024-6-3 14:15

Select Name,Path,Count(Name) From [Sheet1$C1:D18] where Name = 'B' Group by Name,Path

LIUZHU · 发表于 2024-6-3 15:12

没上附件，不好评论，没准不需要SQL也可快速解决。

opiona · 发表于 2024-6-4 21:05

比较笨也比较直观的办法是: 嵌套
虽然对当前问题不是优秀解法

StrSQL="Select Path,Count(Name) AS 数量 From ("
StrSQL=StrSQL & "Select Name,Path From [Sheet1$C1:D18] where Not Name is Null "
StrSQL=StrSQL & ") AS A where Name = 'B'
StrSQL=StrSQL & " Group by Path"

ning84 · 发表于 2024-6-12 03:30

opiona 发表于 2024-6-4 21:05
比较笨也比较直观的办法是: 嵌套
虽然对当前问题不是优秀解法

谢谢你的思路，但没有使用好,目标没有实现实现。

采用循环法，是目标需求结果，但运行时间太长。需要进一步优化。

Function SqlRetuRs(Str)
Dim Cn As ADODB.Connection
Set Cn = New ADODB.Connection
Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
'
If InStr(UCase(Application.Path), "WPS") > 0 Then
Cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
Else
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName
End If
Rs.Open Str, Cn, adOpenKeyset, adLockOptimistic
Set SqlRetuRs = Rs
End Function
'''
Sub ll2()
Dim Str
Dim Rng As Range, oRng As Range
Set Rng = Selection
Dim Sht As Worksheet
Set Sht = Rng.Parent
Set Rng = Sht.Range(Sht.Cells(7, 1).Formula)
Rng.Clear
Set Rng = Sht.Range(Sht.Cells(6, 1).Formula)
Debug.Print Rng.Address
Str = "Select Distinct oDate,Name,Count(Name) "
Str = Str & "From [Sheet1$J13:L54] "
Str = Str & "Where Not Name is Null Group by oDate,Name"
Debug.Print Str
Dim Rs As ADODB.Recordset, Rs1 As ADODB.Recordset
Set Rs = SqlRetuRs(Str)
Sht.Cells(15, 1).CopyFromRecordset Rs
'''
Rs.MoveFirst
For ii = 0 To Rs.RecordCount - 1
If Rs.Fields(2) > 1 Then
Str = "Select Path "
Str = Str & "From [Sheet1$J13:L54] "
Str = Str & "Where Name = '" & Rs.Fields(1) & "'"
Debug.Print Str
Set Rs1 = SqlRetuRs(Str)
aa = Rs1.GetRows()
Rs1.MoveFirst
With WorksheetFunction
Sht.Cells(15 + ii, 5).Resize(, Rs1.RecordCount) = .Transpose(.Transpose(Rs1.GetRows()))
End With
End If
Rs.MoveNext
Next ii
End Sub

复制代码

采用嵌套SQL没有实现。

Sub ll()
Dim Str, oStr
Dim Rng As Range
Set Rng = Selection
Dim Sht As Worksheet
Set Sht = Rng.Parent
Set Rng = Sht.Range(Sht.Cells(7, 1).Formula)
Debug.Print Rng.Address
Rng.Clear
Set Rng = Sht.Range(Sht.Cells(6, 1).Formula)
Debug.Print Rng.Address
SqlShtStr = Sht.Name & "$" & Rng.Address(0, 0)
Dim Rs As ADODB.Recordset, Rs1 As ADODB.Recordset, Rs2 As ADODB.Recordset
Dim StrSql, StrSql1, StrSql2
StrSql1 = "Select Distinct Name From [" & SqlShtStr & "] Where Not Name is Null"
Set Rs1 = SqlRetuRs(StrSql1)
Debug.Print Rs1.RecordCount
''
StrSql = "Select oDate,Name,Path,Count(Name) From ("
StrSql = StrSql & "Select oDate,Name,Path,Count(Name) From [" & SqlShtStr & "] where Not Name is Null Group by oDate, Name,Path "
StrSql = StrSql & ") AS A where "
Rs1.MoveFirst
oStr = "Name = '" & Rs1.Fields(0) & "'"
Rs1.MoveNext
For ii = 1 To Rs1.RecordCount - 1
oStr = oStr & " Or Name = '" & Rs1.Fields(0) & "'"
Rs1.MoveNext
Next ii
StrSql = StrSql & oStr & " Group by oDate,Name,Path"
Debug.Print StrSql
Set Rs = SqlRetuRs(StrSql)
Sht.Cells(15, 1).CopyFromRecordset Rs
End Sub

复制代码

MR_PP · 发表于 2024-6-12 14:57

ning84 发表于 2024-6-12 03:30
谢谢你的思路，但没有使用好,目标没有实现实现。

看下可以吗。

ning84 · 发表于 2024-6-14 00:38

MR_PP 发表于 2024-6-12 14:57
看下可以吗。

谢谢你的帮助。

学会了IIf(COUNT(Name) > 0, MAX(IIf(Path LIKE 'F:\%\%\%', Path, NULL)), NULL) 的应用。

      Str = "SELECT oDate, Name, COUNT(Name) AS Num, "
      Str = Str & "IIf(COUNT(Name) > 0, MAX(IIf(Path LIKE 'F:\%\%\%', Path, NULL)), NULL) AS Path, "
      Str = Str & "IIf(COUNT(Name) > 1, MAX(IIf(Path LIKE 'F:\%\%\%\%', Path, NULL)), NULL) AS Path1, "
      Str = Str & "IIf(COUNT(Name) > 2, MAX(IIf(Path LIKE 'F:\%\%\%\%\%', Path, NULL)), NULL) AS Path2, "
      Str = Str & "IIf(COUNT(Name) > 2, MAX(IIf(Path LIKE 'F:\%\%\%\%\%', Path, NULL)), NULL) AS Path3 "
      Str = Str & SqlShtStr
      Str = Str & "WHERE Name IS NOT NULL "
      Str = Str & "GROUP BY oDate, Name "
      Str = Str & "ORDER BY oDate, Name"

		自动登录	找回密码
密码			免费注册

如何将Group by和Where写在一个SQL中，实现目标结果。

评分