- Set SH1 = Sheets("Data")
- SH1.Range("A2:Z1048576").ClearContents
-
- Str_coon = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\测试.accdb;"
- Rem 季节 品牌 营销单号 系列名称 本厂款号 本厂颜色 下单日期 数量
-
- StrSQL = ""
- StrSQL = StrSQL & "SELECT [季节],[品牌],[营销单号],[系列名称]"
- StrSQL = StrSQL & ",'' AS [本厂款号],'' AS [本厂颜色]"
- StrSQL = StrSQL & ",[下单日期]"
- StrSQL = StrSQL & ",SUM([数量]) AS [数量]"
- StrSQL = StrSQL & " FROM ("
- Rem 防止出现:null
- StrSQL = StrSQL & "SELECT IIF(ISNULL([季节])=TRUE,'',[季节]) AS [季节]"
- StrSQL = StrSQL & ",IIF(ISNULL([品牌])=TRUE,'',[品牌]) AS [品牌]"
- StrSQL = StrSQL & ",IIF(ISNULL([营销单号])=TRUE,'',[营销单号]) AS [营销单号]"
- StrSQL = StrSQL & ",IIF(ISNULL([系列名称])=TRUE,'',[系列名称]) AS [系列名称]"
-
- StrSQL = StrSQL & ",IIF(ISNULL([下单日期])=TRUE,'',[下单日期]) AS [下单日期]"
- StrSQL = StrSQL & ",IIF(ISNULL([数量])=TRUE,0,[数量]) AS [数量]"
- StrSQL = StrSQL & " FROM [营销订单]"
-
- StrSQL = StrSQL & ") GROUP BY [季节],[品牌],[营销单号],[系列名称],[下单日期]"
-
- SQLARR = GET_SQL_To_Arr(StrSQL, Str_coon, False)
-
- StrSQL = "SELECT DISTINCT [季节],[品牌],[营销单号],[系列名称],[本厂款号],[本厂颜色],[下单日期]"
- StrSQL = StrSQL & " FROM ("
- Rem 防止出现:null
- StrSQL = StrSQL & "SELECT IIF(ISNULL([季节])=TRUE,'',[季节]) AS [季节]"
- StrSQL = StrSQL & ",IIF(ISNULL([品牌])=TRUE,'',[品牌]) AS [品牌]"
- StrSQL = StrSQL & ",IIF(ISNULL([营销单号])=TRUE,'',[营销单号]) AS [营销单号]"
- StrSQL = StrSQL & ",IIF(ISNULL([系列名称])=TRUE,'',[系列名称]) AS [系列名称]"
- StrSQL = StrSQL & ",IIF(ISNULL([本厂款号])=TRUE,'',[本厂款号]) AS [本厂款号]"
- StrSQL = StrSQL & ",IIF(ISNULL([本厂颜色])=TRUE,'',[本厂颜色]) AS [本厂颜色]"
- StrSQL = StrSQL & ",IIF(ISNULL([下单日期])=TRUE,'',[下单日期]) AS [下单日期]"
-
- StrSQL = StrSQL & " FROM [营销订单]"
- StrSQL = StrSQL & ")"
- ARX = GET_SQL_To_Arr(StrSQL, Str_coon, False)
-
- Dim STR款号, STR颜色 As String
- Dim BOOL As Boolean
- For X = 0 To UBound(SQLARR, 1)
- STR款号 = ","
- STR颜色 = ","
- BOOL = False
- For I = 0 To UBound(ARX, 1)
- If SQLARR(X, 0) = ARX(I, 0) And SQLARR(X, 1) = ARX(I, 1) And SQLARR(X, 2) = ARX(I, 2) And SQLARR(X, 3) = ARX(I, 3) And SQLARR(X, 6) = ARX(I, 6) Then
- BOOL = True
- If InStr(STR款号, "," & ARX(I, 4) & ",") = 0 Then '// 前后加上[,],防止包含关系
- STR款号 = STR款号 & ARX(I, 4) & ","
- End If
- If InStr(STR颜色, "," & ARX(I, 5) & ",") = 0 Then '// 前后加上[,],防止包含关系
- STR颜色 = STR颜色 & ARX(I, 5) & ","
- End If
-
- End If
- BOOL = False
- Next
- If STR款号 <> "," Then
- STR款号 = VBA.Mid(STR款号, 2, Len(STR款号) - 2)
- End If
- If STR颜色 <> "," Then
- STR颜色 = VBA.Mid(STR颜色, 2, Len(STR颜色) - 2)
- End If
- SQLARR(X, 4) = STR款号
- SQLARR(X, 5) = STR颜色
- Next
- SH1.Range("A2").Resize(UBound(SQLARR, 1) + 1, UBound(SQLARR, 2) + 1) = SQLARR
复制代码 |