楼主
amulee |
Q：如何根据数据透视表页字段的选择自动调整SQL语句？
A：本例中，需要在数据透视表中实现区域排名和总排名。该排名可以通过 [SQL in Excel] 如何在数据透视表中用SQL实现区域排名和总排名？ http://www.exceltip.net/thread-18463-1-1.html
该贴实现。
但数据透视表页字段中选择不同类型的店铺，该排名由于是事先生成的，因而没有变化。但可以通过透视表事件来更新SQL语句从而实现该排名随页字段的变化。
在工作表中添加以下代码：
- Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
- Dim StrPageField As String
- Dim strSql As String
- StrPageField = Target.PageFields(1).CurrentPage.Caption
- Select Case StrPageField
- Case "(全部)"
- strSql = "select a1.*,b1.区域排名,b1.总排名 from [3月1$A:H]a1 " & _
- "Left Join" & _
- "(select *," & _
- "(select count(*)+1 from " & _
- "(select 区域,门店名称,店铺类型,sum(金额) as 金额 from [3月1$] group by 区域,门店名称,店铺类型)a " & _
- "where a.区域=b.区域 and a.金额>b.金额) as 区域排名," & _
- "(select count(*)+1 from " & _
- "(select 区域,门店名称,店铺类型,sum(金额) as 金额 from [3月1$] group by 区域,门店名称,店铺类型)a where a.金额>b.金额) as 总排名 from " & _
- "(select 区域,门店名称,店铺类型,sum(金额) as 金额 from [3月1$] group by 区域,门店名称,店铺类型)b)b1 " & _
- "on a1.区域&"" - ""&a1.门店名称&"" - ""&a1.店铺类型=b1.区域&"" - ""&b1.门店名称&"" - ""&b1.店铺类型 " & _
- "where a1.区域 Is Not Null"
- Case Else
- strSql = "select a1.*,b1.区域排名,b1.总排名 from [3月1$A:H]a1 " & _
- "Left Join" & _
- "(select *," & _
- "(select count(*)+1 from " & _
- "(select 区域,门店名称,店铺类型,sum(金额) as 金额 from [3月1$] Where 类型='" & StrPageField & "' group by 区域,门店名称,店铺类型)a " & _
- "where a.区域=b.区域 and a.金额>b.金额) as 区域排名," & _
- "(select count(*)+1 from " & _
- "(select 区域,门店名称,店铺类型,sum(金额) as 金额 from [3月1$] Where 类型='" & StrPageField & "' group by 区域,门店名称,店铺类型)a where a.金额>b.金额) as 总排名 from " & _
- "(select 区域,门店名称,店铺类型,sum(金额) as 金额 from [3月1$] Where 类型='" & StrPageField & "' group by 区域,门店名称,店铺类型)b)b1 " & _
- "on a1.区域&"" - ""&a1.门店名称&"" - ""&a1.店铺类型=b1.区域&"" - ""&b1.门店名称&"" - ""&b1.店铺类型 " & _
- "where a1.区域 Is Not Null"
- End Select
- Application.EnableEvents = False
- ThisWorkbook.PivotCaches(1).CommandText = strSql
- ThisWorkbook.PivotCaches(1).Refresh
- Application.EnableEvents = True
- End Sub
 数据透视表事件.rar
|