请教各位大佬，这个查询的SQL语句怎么写！感谢！

tel3033

请教各位大佬，这个查询的SQL语句怎么写！感谢！

limonet

关键字：transform

limonet

Sub limonet()
    Dim Cn As Object, StrSQL$
    Set Cn = CreateObject("Adodb.Connection")
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
    StrSQL = "Select 器具名称,状态,Count(*) as 计数 From [Sheet1$B:C] Group By 器具名称,状态 Union All Select 器具名称,'总计',Count(*) From [Sheet1$B:C] Group By 器具名称"
    StrSQL = "TransForm Sum(计数) Select 器具名称 From (" & StrSQL & ") Group By 器具名称 pivot 状态 In('总计','借出','闲置','丢失','损坏')"
    Range("G6").CopyFromRecordset Cn.Execute(StrSQL)
End Sub

cyh3230

大体是这个样子

1. transform count(1)  
   
2. select 器具名称
   
3. from
   
4. (
   
5. select 器具名称,状态  from [Sheet1$A:C]  
   
6. union all
   
7. select 器具名称,"总计" as 状态  from [Sheet1$A:C]  
   
8. )
   
9. where 器具名称<>""
   
10. group by 器具名称  
    
11. pivot 状态

复制代码