|
|
返回结果如下，
如果不对，请告知
| 日期 | 型号 | 生产数 | 不良数 | 销售数量 | | 2012-02-14,2012-02-15,2012-02-16 | A1,A3 | 447 | 24 | | | 2012-02-17 | A3 | 242 | 30 | | | 2012-02-14,2012-02-15,2012-02-16 | A3,A4,A2 | | | 78 | | 2012-02-17 | A1 | | | 23 |
Option Explicit
Sub a()
Dim cnn As New ADODB.Connection, r%, sql$, myf$, I%, M%, T%
Dim RS As New ADODB.Recordset
Dim mycnn As New cConnection
Dim myrs As New cRecordset
Dim SQLA$, SQB$, SQC$
mycnn.CreateNewDB ":memory:"
sql = "create table 生产表(生产日期,型号,生产数,不良数,日期分组)"
mycnn.Execute sql
sql = "create table 销售表(销售日期,型号,销售数量,日期分组)"
mycnn.Execute sql
myf = ThisWorkbook.Path & "\数据源2.xlsx"
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & myf
sql = "SELECT 生产日期,型号,SUM(生产数),SUM(不良数) FROM [表B$A1:D] WHERE 型号 is NOT NULL GROUP BY 生产日期,型号 ORDER BY 1,2"
RS.Open sql, cnn, 1, 1
mycnn.BeginTrans
Do While Not RS.EOF
M = M + 1
For T = 1 To 3
If Not RS.EOF Then
sql = "INSERT INTO 生产表 values ('" & Format(RS.Fields(0), "YYYY-MM-DD") & "','" & RS.Fields(1) & "'," & RS.Fields(2) & "," _
& RS.Fields(3) & "," & M & ")"
mycnn.Execute sql
RS.MoveNext
If RS.EOF Then Exit Do
End If
Next
Loop
cnn.Close
myf = ThisWorkbook.Path & "\数据源3.xlsx"
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & myf
sql = "SELECT 销售日期,型号,SUM(销售数量) FROM [表C$A1:D] WHERE 型号 is NOT NULL GROUP BY 销售日期,型号 ORDER BY 1,2"
RS.Open sql, cnn, 1, 1
M = 0
Do While Not RS.EOF
M = M + 1
For T = 1 To 3
If Not RS.EOF Then
sql = "INSERT INTO 销售表 values ('" & Format(RS.Fields(0), "YYYY-MM-DD") & "','" & RS.Fields(1) & "'," & RS.Fields(2) & "," & M & ")"
mycnn.Execute sql
RS.MoveNext
If RS.EOF Then Exit Do
End If
Next
Loop
mycnn.CommitTrans
sql = "CREATE TABLE T (日期,型号)"
mycnn.Execute sql
sql = "insert into T SELECT GROUP_CONCAT(生产日期),GROUP_CONCAT(DISTINCT(型号)) as 型号 FROM 生产表 GROUP BY 日期分组 "
mycnn.Execute sql
sql = "INSERT INTO T SELECT GROUP_CONCAT(销售日期),GROUP_CONCAT(DISTINCT(型号)) as 型号 FROM 销售表 GROUP BY 日期分组"
mycnn.Execute sql
Sheet5.Activate
Cells.Clear
SQA = "(SELECT GROUP_CONCAT(生产日期) AS 生产日期,GROUP_CONCAT(DISTINCT(型号)) as 型号,SUM(生产数) AS 生产数,SUM(不良数) AS 不良数 FROM 生产表 GROUP BY 日期分组) B"
SQB = "(SELECT GROUP_CONCAT(销售日期) AS 销售日期,GROUP_CONCAT(DISTINCT(型号)) as 型号,SUM(销售数量) AS 销售数量 FROM 销售表 GROUP BY 日期分组) C"
SQC = "(SELECT DISTINCT 日期,型号 FROM T) A"
sql = "SELECT A.日期,A.型号,B.生产数,B.不良数,C.销售数量 FROM " & SQC & " left join " & SQA & " on A.日期=B.生产日期 and A.型号=B.型号" _
& " left join " & SQB & " on A.日期=C.销售日期 AND A.型号=C.型号"
MsgBox sql
myrs.OpenRecordset sql, mycnn
For I = 0 To myrs.Fields.Count - 1
Cells(1, I + 1) = myrs.Fields(I).Name
Next
Range("a2").CopyFromRecordset myrs.GetADORsFromContent
cnn.Close
Set RS = Nothing
Set cnn = Nothing
Set mycnn = Nothing
Set myrs = Nothing
End Sub
|
评分
-
1
查看全部评分
-
|
楼主: cjq253
沪公网安备 31011702000001号 沪ICP备11019229号-2
楼主
发表于 2019-2-20 15:31
