table表
company result
a y
b y
a n
b n
a y
我想用MSHFLEX控件来这样显示
公司名称 总数 成功数 不成功数 成功率
a 3 2 1 33.3%
b 2 1 1 33.3%
我连接数据库的方式是ADO
请问如何实现?
谢谢!
company result
a y
b y
a n
b n
a y
我想用MSHFLEX控件来这样显示
公司名称 总数 成功数 不成功数 成功率
a 3 2 1 33.3%
b 2 1 1 33.3%
我连接数据库的方式是ADO
请问如何实现?
谢谢!
Private Sub initgrid()
MSFlexGrid1.Clear
MSFlexGrid1.Cols = 5
MSFlexGrid1.Rows = 2
MSFlexGrid1.TextMatrix(0, 0) = "公司名称"
MSFlexGrid1.TextMatrix(0, 1) = "总数"
MSFlexGrid1.TextMatrix(0, 2) = "成功数"
MSFlexGrid1.TextMatrix(0, 3) = "不成功数"
MSFlexGrid1.TextMatrix(0, 4) = "成功率" AddData "a"
AddData "b"
End Subsub AddData(companyName as string)
dim rs as new adodb.recordset
dim strSQl as string
dim aa as integer
dim bb as integer
strSQl="select * from table where company='"& companyName &"'"
rs.Open strSQl, cn, adOpenDynamic, adLockOptimistic
MSFlexGrid1.TextMatrix(1, 0) = "a"
MSFlexGrid1.TextMatrix(1, 1) = rs.recordcount
aa=rs.recordcount
rs.close strSQl="select * from table where company='"& companyName &"' and result='y'"
rs.Open strSQl, cn, adOpenDynamic, adLockOptimistic
MSFlexGrid1.TextMatrix(1, 2) = rs.recordcount
bb=rs.recordcount
MSFlexGrid1.TextMatrix(1, 3)=int(aa)-int(bb) MSFlexGrid1.TextMatrix(1, 4)=Format(bb/aa#, "0。00%")
end sub
dim strSql as stringstrSql="select company as 公司名称,sum(1) as 总数,
sum(case result when 'y' then 1 else 0 end) as 成功数 ,
sum(case result when 'n' then 1 else 0 end) as 不成功数,
cast(100*cast(sum(case result when 'y' then 1 else 0 end)*1.0/sum(1) as decimal(10,3)) as varchar(10))+'%' as 成功率
from ta
group by company"rs.open strsql,cn
set mshflexgrid.datasource=rs
rs.close
那么应该怎么统计成功数
望指教