*这个问题在stackoverflow中已经问过
我已经修改并在这里再次询问*
1)“ SELECT officer, null, sum(mkt), Sum(Non), sum(ICP), (sum(mkt)+Sum(Non)+sum(ICP)) as total From [DB$] group by officer
” <=他们还好。他们应该按军官分组,因为每个军官都有很多记录
2)对于字段mkt,它不一定由每个记录中的值组成。当mkt由价值组成,例如30时,表示该军官从事现场工作。因此,我应该计算他的现场工作时间-Totalmin。
officer mkt Totalmin
S29 30 50
S29 60
S29 70 80
S29 80
S55 30 80
S55 60
S55 70 80
S55 80
因此,结果应为:
officer sum(totalmin)
s29 130
s55 160
您可以在这里想象。如果Mkt字段由数字组成,则总分钟数将加起来成为必需的分钟数。
在图片中,最低要求的输出应按45+40=85
人员分组(staffid-186)
怎么写这个sql?
而且,问题是我必须在同一个sql中合并这两种情况。我怎样才能?
您可以在select中使用子查询来执行以下任务:
Sub SQL()
Dim cn As Object: Set cn = CreateObject("ADODB.Connection")
Dim rs As Object: Set rs = CreateObject("ADODB.Recordset")
Dim tbl1$, QT, strFile$, strCon$, strSQL$
strFile = ThisWorkbook.FullName 'if not active file then specify path!!!
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
cn.Open strCon
tbl1 = "[Sheet1$" & Sheets("Sheet1").UsedRange.Address(0, 0) & "]" 'replace sheet1 with required sheet name
strSQL = "SELECT o.officer ," & _
" NULL ," & _
" SUM(o.mkt) ," & _
" SUM(o.Non) ," & _
" SUM(o.ICP) ," & _
" ( SELECT SUM(i.Totalmin) FROM " & tbl1 & " AS i " & _
" WHERE i.Mkt > 0 AND i.officer = o.officer) AS TotalMin " & _
" FROM " & tbl1 & " AS o " & _
" GROUP BY o.officer"
rs.Open strSQL, cn
Workbooks.Add
Set QT = ActiveSheet.QueryTables.Add(rs, ActiveSheet.[A1]): QT.Refresh: QT.Delete
rs.Close
cn.Close
End Sub
我已经测试了这种方法,因此它也适合您:
来源:
输出:
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句