SQL分类统计

donghui2363

你测试一下

1. Sub 统计()
   
2. Set cnn = CreateObject("ADODB.Connection")
   
3.     With cnn
   
4.         .Provider = "Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0"
   
5.         .ConnectionString = ThisWorkbook.FullName
   
6.         .Open
   
7.     End With
   
8.     sql1 = "select 推荐人,count(记账日期) as 新客,sum(金额) as 新金 from (select 推荐人,记账日期,sum(消费金额) as 金额 from [1-2$a1:f] where 新老='新客' and (记账日期>=#2020/7/15# and 记账日期<=#2020/8/14#) group by 推荐人,记账日期) group by 推荐人"
   
9.     SQL2 = "select 推荐人,count(记账日期) as 老客,sum(金额) as 老金 from (select 推荐人,记账日期,sum(消费金额) as 金额 from [1-2$a1:f] where 新老='老客' and (记账日期>=#2020/7/15# and 记账日期<=#2020/8/14#) group by 推荐人,记账日期) group by 推荐人"
   
10.     Sql = "select a.推荐人,新客,新金,老客,老金 from (" & sql1 & ") b right join (" & SQL2 & ") a on a.推荐人=b.推荐人"
    
11.     Range("h2").CopyFromRecordset cnn.Execute(Sql)
    
12.     cnn.Close
    
13.     Set cnn = Nothing
    
14. End Sub

复制代码

末将

cui26896 发表于 2020-10-17 15:56
要审核，只能重发更改了几个字的代码，我用字典方法核对过了，应该没有问题。

再次谢谢崔老师，我是这个意思，推荐人S推荐了A.B.C三个客户，
A1日消费了10元和20元两笔，2日消费了30元一笔，记2人天，共60元，
B3日消费了40元一笔，                                         记1人天，共40元，
C4日消费了15元一笔，5日消费了5元和8元两笔，    记2人天，共28元，共计S推荐人就是5人天，消费总额128元，
看看能不能帮我再改一下？

末将

我写的这样，但不知哪里出了错，请老师指点一下：
transform count(客户编号),sum(金额) select 推荐人 from (SELECT 客户编号,记账日期,推荐人,新老,sum(消费金额) as 金额 FROM [1-2$] where 记账日期 between #2020/7/15# and #2020/8/14# group by 推荐人,新老,客户编号,记账日期) group by 推荐人 pivot 新老
是transform后面只能跟一个字段吗？我把count(客户编号),删了，只统计金额就没问题

cui26896

SQL是数据库引用到excel中，你如果对数据库不了解，可以找一下认论中相关帖去了解一下，数据库对表的要求是有规则的，在表中同一天不可能出现两个时间和推荐人完全相同的两个记录，这是输入数据发生了错误，你非要数据库的原则来适合于你的要求，那是肯定做不到的。但是VBA循环，可以对一行特殊的数据做处理，所以，用数组字典循环起来是可以解决你的问题的，但是你指明要SQL，这我确实是无能为力。

cui26896

末将 发表于 2020-10-17 16:57
再次谢谢崔老师，我是这个意思，推荐人S推荐了A.B.C三个客户，
A1日消费了10元和20元两笔，2日消费了30 ...

你这样的要求，我只能用数组字典方法来解决。用SQL很困难。

donghui2363

末将 发表于 2020-10-17 16:57
再次谢谢崔老师，我是这个意思，推荐人S推荐了A.B.C三个客户，
A1日消费了10元和20元两笔，2日消费了30 ...

我这个不符合要求吗？

末将

select ts.推荐人,ts.新客 as 新客人天数,je.新客 as 新客金额,ts.老客 as 老客人天数,je.老客 as 老客金额 from (transform count(客户编号) select 推荐人 from (SELECT 客户编号,记账日期,推荐人,新老,sum(消费金额) as 金额 FROM [1-2$] where 记账日期 between #2020/7/15# and #2020/8/14# group by 推荐人,新老,客户编号,记账日期) group by 推荐人 pivot 新老) ts inner join (transform sum(消费金额) select 推荐人 FROM [1-2$] where 记账日期 between #2020/7/15# and #2020/8/14#  group by 推荐人 pivot 新老) je on ts.推荐人=je.推荐人

这样写，为什么不行呢？是用了transform以后，就不能再被查询了吗？

microyip

1. Sub 统计1()
   
2.     Dim oConn As Object, sSQL As String
   
3.    
   
4.     Set oConn = CreateObject("ADODB.Connection")
   
5.     oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0';Data Source=" & ThisWorkbook.FullName
   
6.     sSQL = "Select  [推荐人],[新老],[记账日期],Sum([消费金额]) As 总额 From [1-2$A1:F] " & _
   
7.         "Where [记账日期] Between #" & [O2] & "# And #" & [P2] & "# Group by [推荐人],[新老],[记账日期]"
   
8.     sSQL = "Select  [推荐人],Sum(IIF([新老]='新客',1,0)),Sum(IIF([新老]='新客',[总额],0)),Sum(IIF([新老]='老客',1,0)),Sum(IIF([新老]='老客',[总额],0)) " & _
   
9.         "From (" & sSQL & ") " & _
   
10.         "Group by [推荐人]"
    
11.     [H2:L2].Resize(ActiveSheet.UsedRange.Rows.Count).ClearContents
    
12.     [H2].CopyFromRecordset oConn.Execute(sSQL)
    
13.     oConn.Close
    
14.     Set oConn = Nothing
    
15. End Sub

复制代码

microyip

附上附件以供参考

末将

microyip 发表于 2020-10-18 13:38

哦，原来就用Sum(IIF(,,0))就解决了，是我想多了，多谢侠圣老师