• 主页

# 如何用存儲過程實現這個功能

dongfang2003 2004-10-06 11:16:17

sno fmno(字段名)
a1 m1
a2 m2
a3 m3
a1 m2
a1 m3
a1 m4
a1 m5
a1 ..
a2 ..
a3 ..

a1用到了m1,m2,m3,m4,m5,..
a2用到了m2,..
a3用到了m3,..

a1對應
fmno1 fmno2 fmno3(字段)
m1 m2 m3
m4 m5 ....
a2對應
fmno1 fmno2 fmno3(字段)
m2 ..
a3對應
fmno1 fmno2 fmno3(字段)
m3 ..

...全文
86 点赞 收藏 8

8 条回复

dongfang2003 2004-10-22

dongfang2003 2004-10-08

dongfang2003 2004-10-07

wumylove1234 2004-10-06

bxf 2004-10-06
Public Function Bxf_Hb(Str_tj As String) As String
Dim zds As Long
Dim zd() As String
Dim strs() As String
Dim strA As String
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str3A As String
Dim i As Integer
Set rs1 = readrs("select 项目 from 收款记录表 where " & Str_tj & " group by 项目")
zds = rs1.RecordCount
ReDim zd(zds)
ReDim strs(zds)
i = 0
str1 = "from (select 科别 from 收款记录表 where " & Str_tj & " group by 科别) as tt1"
str2 = ""
str3 = ""
strA = ""
str3A = ""
Do While Not rs1.EOF
i = i + 1
zd(i) = Trim(rs1(0))
strs(i) = "(select 科别,sum(金额) as [" & zd(i) & "] from 收款记录表 where 项目='" & zd(i) & _
"' and " & Str_tj & " group by 科别 ) as t" & i
strA = strA & "sum([" & zd(i) & "]) as [" & zd(i) & "],"
str1 = str1 & " LEFT OUTER JOIN " & strs(i) & " ON tt1.科别 = t" & i & ".科别"
str2 = str2 & "isnull([" & zd(i) & "],0) as [" & Trim(zd(i)) & "],"
str3 = str3 & "isnull([" & zd(i) & "],0) +"
str3A = str3A & "sum(isnull([" & zd(i) & "],0)) +"
rs1.MoveNext
Loop
If Len(str3) > 1 Then str3 = "(" & Left(str3, Len(str3) - 1) & ") as 合计 "
If Len(str2) = 0 Then str2 = " * "
str1 = "select tt1.科别," & str2 & str3 & str1
'If Trim(strA) <> "" Then strA = Left(strA, Len(strA) - 1)
If Trim(str3A) <> "" Then str3A = Left(str3A, Len(str3A) - 1)
strA = "select '合计' as 科别," & strA & str3A & " from ( " & str1 & " ) as temp1"
Bxf_Hb = "( " & str1 & " ) union all ( " & strA & " )"
'Debug.Print strA
End Function

dongfang2003 2004-10-06

ryuginka 2004-10-06

dongfang2003 2004-10-06

1180

VB 数据库(包含打印，安装，报表)