【SQL查询】vba如何通过SQL计算每一行的数值占比？

stevefigo

【疑问】请教大神，想求每个客户的收入占总收入之比，并且是运用vba中的SQL查询提取数据出来，要怎么操作呢？eg：检测业务收入占所有业务收入的占比、检测业务收入中的A、B、C...客户占检测业务收入的占比，如图（详见附件）

stevefigo

忘记附上代码了，如下：（错的，要改，不清楚怎么改）

Sub test()


    Dim cnn As Object, SQL$
    Set cnn = CreateObject("adodb.connection")
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName

Data = "科目名称,本期借方发生额,本年借方累计,本年借方累计/max(本年借方累计)"

Table = "[辅助项目核算科目余额表$a1:k5]"
      
      case1 = "科目代码 like '1122%'"
      case2 = "本年借方累计 > 0 "
         
      SQL = "select " & Data & " from " & Table & _
      "where (" & case1 & " and " & case2 & ") " & _
      "group by 本年借方累计,本期借方发生额,科目名称 order by 本年借方累计 DESC, 科目名称 DESC"

   
      Sheets("收入明细").Cells(6, 2).CopyFromRecordset cnn.Execute(SQL)

End Sub

cui26896

一头雾水，不懂财务，10000是从哪里来的，看不懂。

stevefigo

cui26896 发表于 2020-2-13 12:37
一头雾水，不懂财务，10000是从哪里来的，看不懂。

应收账款那一行的10000只是随意写的，理论上会在“辅助项目核算科目余额表”中反映出来。此处为方便而已，不用考虑该数字从哪里来

cui26896

stevefigo 发表于 2020-2-13 15:55
应收账款那一行的10000只是随意写的，理论上会在“辅助项目核算科目余额表”中反映出来。此处为方便而已 ...

如果该数与要取数所在表的聚合相关，不可能不考虑。这里要求是用SQL,不是靠数组循环。

chis3

sql好像不是很多函數,有否這樣聚合

shenjianrong163

加一个数组计算一下：

1. Sub test()
   
2.     Dim cnn As Object, SQL$, i%, arr
   
3.     Set cnn = CreateObject("adodb.connection")
   
4.     cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
   
5.     Data = "科目名称,本期借方发生额,本年借方累计,本年借方累计/max(本年借方累计)"
   
6.     Table = "[辅助项目核算科目余额表$a1:k5]"
   
7.     case1 = "科目代码 like '1122%'"
   
8.     case2 = "本年借方累计 > 0 "
   
9.     SQL = "select " & Data & " from " & Table & _
   
10.     "where (" & case1 & " and " & case2 & ") " & _
    
11.     "group by 本年借方累计,本期借方发生额,科目名称 order by 本年借方累计 DESC, 科目名称 DESC"
    
12.     arr = cnn.Execute(SQL).GetRows
    
13.     For i = 1 To UBound(arr, 2)
    
14.         arr(3, i) = arr(2, i) / arr(2, 0)
    
15.     Next
    
16.     Sheets("收入明细").[B6].Resize(UBound(arr), 4) = Application.Transpose(arr)
    
17.     Sheets("收入明细").Cells(6, 5) = Sheets("收入明细").Cells(6, 4) / Sheets("收入明细").Cells(5, 4)
    
18. End Sub
    

复制代码

microyip

1. Sub test()
   
2. 
   
3. 
   
4.     Dim cnn As Object, SQL$
   
5.     Set cnn = CreateObject("adodb.connection")
   
6.     cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
   
7. 
   
8.     Data = "科目名称,sum(本期借方发生额),sum(本年借方累计),sum(本年借方累计)/sum(本年借方累计)"
   
9. 
   
10.     Table = "[辅助项目核算科目余额表$a1:k5]"
    
11.       
    
12.       case1 = "科目代码 like '1122%'"
    
13.       case2 = "本年借方累计 > 0 "
    
14.          
    
15.       SQL = "select " & Data & " from " & Table & _
    
16.       "where (" & case1 & " and " & case2 & ") " & _
    
17.       "group by 科目名称 order by sum(本年借方累计) DESC, 科目名称 DESC"
    
18. 
    
19.    
    
20.       Sheets("收入明细").Cells(6, 2).CopyFromRecordset cnn.Execute(SQL)
    
21. 
    
22. End Sub

复制代码

cui26896

microyip 发表于 2020-2-13 17:16

运行老师的代码与原来一样，没有变化。

cui26896

microyip 发表于 2020-2-13 17:16

运行老师的代码结果与原表的一样，没有变化。