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

dongfang2003 2004-10-06 11:16:17
表t1
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,..
要求把sno用到的fmno存在一個臨時表中(要求3行一存)
a1對應
fmno1 fmno2 fmno3(字段)
m1 m2 m3
m4 m5 ....
a2對應
fmno1 fmno2 fmno3(字段)
m2 ..
a3對應
fmno1 fmno2 fmno3(字段)
m3 ..
請問如何用存儲過程做到
...全文
86 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
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 rs1 As ADODB.Recordset
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
这是我用VB写的(速度慢),和你要的比较接近,你可以改成存储过程
回复
dongfang2003 2004-10-06
請把結果詳細寫出來,我知道用游标,也用過游标,但對這個問題我不知道如何寫出存儲過程,急!
回复
ryuginka 2004-10-06
游标
回复
dongfang2003 2004-10-06
請問如何做到
回复
发动态
发帖子

1180

社区成员

VB 数据库(包含打印,安装,报表)
申请成为版主
社区公告
暂无公告