Declare @F1 varchar(8000),@F2 varchar(8000) set @F1='' set @F2='' Select @F1=Cast(ID as varchar) +','+@F1 from 表1 order by ID Select @F2=cast(A.值 as varchar)+','+@F2 from 表2 A left join 表1 B on(A.ID=B.ID) order by A.ID Select @F1,@F2 這是在SQL server 中的寫法﹐希望對你有幫助
按照changechange(http://access911.net 是我的个人网站,欢迎光临)的提示在ACCESS中写了一个, 表1:apptobu app bucode 1 CCCC 2 AAAA 3 BBBB 4 DDDD 6 EEEE 7 FFFF表2:testapp strApp 1,2,3 4,5,6,1Public Function return_sl(dq As String) As String Dim strA As String Dim rs As Recordset Dim strSql As String Dim db As Database
Set db = CurrentDb()
Do While InStr(1, dq, ",") > 0 strSql = "select * from apptobu where app='" + Mid(dq, 1, InStr(1, dq, ",") - 1) + "'" Set rs = db.OpenRecordset(strSql) If Not rs.EOF Then strA = strA + "," + rs!bucode Else strA = strA + ",NULL" End If dq = Mid(dq, InStr(1, dq, ",") + 1) Loop strSql = "select * from apptobu where app='" + dq + "'" Set rs = db.OpenRecordset(strSql) If Not rs.EOF Then strA = strA + "," + rs!bucode Else strA = strA + ",NULL" End If
rs.Close Set rs = Nothing return_sl = Mid(strA, 2) End Functionselect strapp,return_sl(strapp) from testapp
传入参数为F1的值,如:3,4,5,把这个串作为参数传给GetValue()
2、在GetValue()内部拆分这个串,分别从表2中获取值,并连接成一个串,作为函数的返回值public fucntion GetValue(string InputString)
'拆分InputString
'记录集循环获取每个ID的值,并连成一个串
'返回此串
end function
新手来看:如何实现文字的sum?《VBA》
http://access911.net/index.asp?u1=a&u2=77FABF1E13DC
set @F1=''
set @F2=''
Select @F1=Cast(ID as varchar) +','+@F1 from 表1 order by ID
Select @F2=cast(A.值 as varchar)+','+@F2 from 表2 A left join 表1 B on(A.ID=B.ID) order by A.ID
Select @F1,@F2
這是在SQL server 中的寫法﹐希望對你有幫助
表1:apptobu
app bucode
1 CCCC
2 AAAA
3 BBBB
4 DDDD
6 EEEE
7 FFFF表2:testapp
strApp
1,2,3
4,5,6,1Public Function return_sl(dq As String) As String
Dim strA As String
Dim rs As Recordset
Dim strSql As String
Dim db As Database
Set db = CurrentDb()
Do While InStr(1, dq, ",") > 0 strSql = "select * from apptobu where app='" + Mid(dq, 1, InStr(1, dq, ",") - 1) + "'"
Set rs = db.OpenRecordset(strSql)
If Not rs.EOF Then
strA = strA + "," + rs!bucode
Else
strA = strA + ",NULL"
End If
dq = Mid(dq, InStr(1, dq, ",") + 1)
Loop
strSql = "select * from apptobu where app='" + dq + "'"
Set rs = db.OpenRecordset(strSql)
If Not rs.EOF Then
strA = strA + "," + rs!bucode
Else
strA = strA + ",NULL"
End If
rs.Close
Set rs = Nothing return_sl = Mid(strA, 2)
End Functionselect strapp,return_sl(strapp) from testapp