1,216
社区成员
发帖
与我相关
我的任务
分享着急中，请教两条语句如何写？一条是关于sql的。
1.是关于SQL的:假设一个表中有三个栏为,(名字,编号,成绩)
现在要显示出5个连续的记录,
条件为这5个连续的记录,它们的成绩总和是最大的.
2.假设有一个字符串
str="<A>******</A><B>********</B>......<Z>*******</Z>"
其中*为任意的字符,"..."为省略
要求为显示出<B>********</B>中两个<B></B>中间的内容.
(在<B></B>中间可能有出现<B></B>中间,也可能没有)
请教了。
现在要显示出5个连续的记录,
条件为这5个连续的记录,它们的成绩总和是最大的.
2.假设有一个字符串
str="<A>******</A><B>********</B>......<Z>*******</Z>"
其中*为任意的字符,"..."为省略
要求为显示出<B>********</B>中两个<B></B>中间的内容.
(在<B></B>中间可能有出现<B></B>中间,也可能没有)
请教了。
...全文
请发表友善的回复…
发表回复
lsftest 2004-04-10
- 打赏
- 举报
1.select top 5 * from table order by [成绩字段] desc;
KJ_Wang 2004-04-10
- 打赏
- 举报
--No2 is easy
First: You can write a function ,so that it can find first <B> and </B> Position
Second:you use method you studied in collage,Easily get the answner.OK?
First: You can write a function ,so that it can find first <B> and </B> Position
Second:you use method you studied in collage,Easily get the answner.OK?
KJ_Wang 2004-04-10
- 打赏
- 举报
--No1.（SQL Server Database）
--Now Create Table ,Code Following:
if exists(select * from sysobjects where id=object_id('dbo.TableName))
drop TableName
go
create TableName
(
No nvarchar(30) not null,
Name nvarchar(50) null,
Grade decimal(13,4)
)
--Procedure Code Following
if exists(select * from sysobjects where id=object_id('dbo.ProcecureName'))
drop ProcedureName
go
create Procedure ProcdureName
as
--Create Temp Table,So that it Can stored temp five records
create Table #TmpTable
(
No nvarchar(30) not null,
Name nvarchar(50) null,
Grade decimal(13,4)
)
declare @cnt int
declare @tmpNo1 nvarchar(30)--Stored Previous No
declare @tmpNo2 nvarchar(30)--Stored Current No
declare @TmpValue1 decimal(13,4)--Stored Previous Vlaue
declare @TmpValue2 decimal(13,4)--Stored Current Value
select @cnt=5
declare C1 scroll cursor for select No from TableName order by No
fetch nextfrom C1 into @tmpNo1
fetch absolute @cnt from C1 into @tmpNo2
select @TmpValue1=sum(Grade) from TableName where No>=@tmpNo1 and No<=@tmpNo order by No
while @@fetch_status=0
begin
fetch nextfrom C1 into @tmpNo1
select @cnt=@cnt+5
fetch absolute @cnt from C1 into @tmpNo2
select @TmpValue2=sum(Grade) from TableName where No>=@tmpNo1
and No<=@tmpNo order by No
if @TmpValue2>@TmpValue1
begin
delete from #TmpTable
insert into #TmpTable select No,Name,Grade from TableName where No>=
@TmpNo1 and @TmpNo2>=No
select @TmpValue1=@TmpValue2
end
end
close C1
deallocate C1
Insert into TableName select * from #TmpTable
go
'=========VB Program Code=========
dim rs as new adodb.recordset
cn.execute "ProcedureName"
set rs=cn.execute("select * from TableName")
msgbox rs.recordcount
--No Testing,Logistic is True,if not use ,you can send message to me
--Now Create Table ,Code Following:
if exists(select * from sysobjects where id=object_id('dbo.TableName))
drop TableName
go
create TableName
(
No nvarchar(30) not null,
Name nvarchar(50) null,
Grade decimal(13,4)
)
--Procedure Code Following
if exists(select * from sysobjects where id=object_id('dbo.ProcecureName'))
drop ProcedureName
go
create Procedure ProcdureName
as
--Create Temp Table,So that it Can stored temp five records
create Table #TmpTable
(
No nvarchar(30) not null,
Name nvarchar(50) null,
Grade decimal(13,4)
)
declare @cnt int
declare @tmpNo1 nvarchar(30)--Stored Previous No
declare @tmpNo2 nvarchar(30)--Stored Current No
declare @TmpValue1 decimal(13,4)--Stored Previous Vlaue
declare @TmpValue2 decimal(13,4)--Stored Current Value
select @cnt=5
declare C1 scroll cursor for select No from TableName order by No
fetch nextfrom C1 into @tmpNo1
fetch absolute @cnt from C1 into @tmpNo2
select @TmpValue1=sum(Grade) from TableName where No>=@tmpNo1 and No<=@tmpNo order by No
while @@fetch_status=0
begin
fetch nextfrom C1 into @tmpNo1
select @cnt=@cnt+5
fetch absolute @cnt from C1 into @tmpNo2
select @TmpValue2=sum(Grade) from TableName where No>=@tmpNo1
and No<=@tmpNo order by No
if @TmpValue2>@TmpValue1
begin
delete from #TmpTable
insert into #TmpTable select No,Name,Grade from TableName where No>=
@TmpNo1 and @TmpNo2>=No
select @TmpValue1=@TmpValue2
end
end
close C1
deallocate C1
Insert into TableName select * from #TmpTable
go
'=========VB Program Code=========
dim rs as new adodb.recordset
cn.execute "ProcedureName"
set rs=cn.execute("select * from TableName")
msgbox rs.recordcount
--No Testing,Logistic is True,if not use ,you can send message to me
leolan 2004-04-10
- 打赏
- 举报
1.是关于SQL的:假设一个表中有三个栏为,(名字,编号,成绩)
现在要显示出5个连续的记录,
条件为这5个连续的记录,它们的成绩总和是最大的.
/***********************獲取第一條記錄的位置***************************/
if not (select object_id('tempdb..#T')) is null drop table #T
select * into #T from 表
go
declare @i int , @sum int ,@j int , @a int
select @i=1 , @j=0
while @i< = (select count(*)-4 from 表)
begin
select @sum = sum( 成績) from ( select top 5 * from #T ) A
if @sum > @j
begin
set @j=@sum
set @a=@i
end
delete from #T where 名字 = (select top 1 名字 from #T)
set @i=@i+1
end
print N'最大成绩总和: ' + convert ( Nvarchar , @j )
print N'從第 ' + convert ( Nvarchar , @a ) + N' 條起後的5條記錄'
go
--drop table 表
set nocount off
现在要显示出5个连续的记录,
条件为这5个连续的记录,它们的成绩总和是最大的.
/***********************獲取第一條記錄的位置***************************/
if not (select object_id('tempdb..#T')) is null drop table #T
select * into #T from 表
go
declare @i int , @sum int ,@j int , @a int
select @i=1 , @j=0
while @i< = (select count(*)-4 from 表)
begin
select @sum = sum( 成績) from ( select top 5 * from #T ) A
if @sum > @j
begin
set @j=@sum
set @a=@i
end
delete from #T where 名字 = (select top 1 名字 from #T)
set @i=@i+1
end
print N'最大成绩总和: ' + convert ( Nvarchar , @j )
print N'從第 ' + convert ( Nvarchar , @a ) + N' 條起後的5條記錄'
go
--drop table 表
set nocount off
northwolves 2004-04-09
- 打赏
- 举报
2。
'add a textbox and a commandbutton to form1
Private Sub Command1_Click()
MsgBox between(Text1.Text, "<B>", "</B>")
End Sub
Function between(ByVal x As String, splitA As String, SPLITB As String) As String
Dim TEMP, I As Long
between = ""
x = Replace(x, SPLITB, splitA)
TEMP = Split(x, splitA)
For I = 1 To UBound(TEMP) Step 2
between = between & TEMP(I) & vbCrLf
Next
Set TEMP = Nothing
End Function
Private Sub Form_Load()
Text1 = "<A>******</A><B>ABCDEFG</B>......<Z>*******</Z><B>1234567</B>"
End Sub
'add a textbox and a commandbutton to form1
Private Sub Command1_Click()
MsgBox between(Text1.Text, "<B>", "</B>")
End Sub
Function between(ByVal x As String, splitA As String, SPLITB As String) As String
Dim TEMP, I As Long
between = ""
x = Replace(x, SPLITB, splitA)
TEMP = Split(x, splitA)
For I = 1 To UBound(TEMP) Step 2
between = between & TEMP(I) & vbCrLf
Next
Set TEMP = Nothing
End Function
Private Sub Form_Load()
Text1 = "<A>******</A><B>ABCDEFG</B>......<Z>*******</Z><B>1234567</B>"
End Sub
