34,590
社区成员
发帖
与我相关
我的任务
分享
建表及插入记录
create table tb(AA int,BB varchar(10),CC int,DD int)
insert into tb(aa,bb,cc,dd) select 1,'F01',12,15
insert into tb(aa,bb,cc,dd) select 2,'F01',16,18
insert into tb(aa,bb,cc,dd) select 3,'F02',19,21
insert into tb(aa,bb,cc,dd) select 4,'F03',22,25
insert into tb(aa,bb,cc,dd) select 5,'F01',26,28
insert into tb(aa,bb,cc,dd) select 6,'F03',29,31
go
新建模块,输入下列程序:
Option Compare Database
Public Function GroupConcat(sColumn As String)
Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim sResult As String
sResult = ""
sSQL = "select cc,dd from tb where bb=""" & sColumn & """"
rs.Open sSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do While Not rs.EOF
If sResult <> "" Then
sResult = sResult & "/"
End If
sResult = sResult & CStr(rs.Fields(0).Value) & "-" & CStr(rs.Fields(1).Value)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
GroupConcat = sResult
End Function
新建查询,在SQL窗口输入下列查询语句:
SELECT tb.BB,sum(DD-CC+1) as FF,GroupConcat(bb) AS GG
FROM tb
GROUP BY tb.BB;go
drop table tb
查询结果
BB FF GG
F01 10 12-15/16-18/26-28
F02 3 19-21
F03 7 22-25/29-31
--只会前两个列,最后一个列好像得写VB函数
select
ee=bb,
ff=sum(dd-cc+1)
from tb
group by bb
select EE=BB,
FF=sum(DD-CC+1),
GG=SUM(CC-DD)
FROM 表 group by BB