SQL实现香川多列组合

张雄友

SQL实现香川多列组合。

1. Sub SQL也能实现组合()
   
2.     tt = Timer
   
3.     Dim Conn As Object, Rst As Object, r&
   
4.     Dim strConn As String, strSQL As String
   
5.     Dim i As Integer, PathStr As String
   
6.     Set Conn = CreateObject("ADODB.Connection")
   
7.     Set Rst = CreateObject("ADODB.Recordset")
   
8.     PathStr = ThisWorkbook.FullName
   
9.     Select Case Application.Version * 1
   
10.     Case Is <= 11
    
11.         strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
    
12.     Case Is >= 12
    
13.         strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
    
14.     End Select
    
15.     Conn.Open strConn
    
16.     '非最大行号全部要忽略空值！
    
17.     strSQL = "select * from [Data$A1:A10],[Data$B1:B10],[Data$C1:C10],[Data$D1:D10],[Data$E1:E12] " & _
    
18.             "where len(前天) and len(昨天) and len(今天) and len(明天) "
    
19.     Set Rst = Conn.Execute(strSQL)
    
20.     With Sheets("Result")
    
21.         .Cells.Clear
    
22.         For i = 0 To Rst.Fields.Count - 1
    
23.             .Cells(1, i + 1) = Rst.Fields(i).Name
    
24.         Next i
    
25.         .Range("A2").CopyFromRecordset Rst
    
26.         .Cells.EntireColumn.AutoFit
    
27.         r = Range("A1048576").End(xlUp).Row - 1
    
28.     End With
    
29.     Rst.Close
    
30.     Conn.Close
    
31.     Set Conn = Nothing
    
32.     Set Rst = Nothing
    
33.     MsgBox "用时" & Format(Timer - tt, "0.00") & "秒" & Chr(10) & "共生成" & r & "种组合！"
    
34. End Sub
    

复制代码

xiaohuaxiong

你好，大概意思是SQL 多表连接生产笛卡尔积，下面的where语句怎么理解呢，不是很清楚，谢谢

where len(前天) and len(昨天) and len(今天) and len(明天)

yzxfelix

[Data$A1:A10],[Data$B1:B10],[Data$C1:C10],[Data$D1:D10],[Data$E1:E12]这个范围不定怎么整