求某字段的值较大的前三条记录?急问

daisy0432 2003-10-19 05:13:35
有这样的表:
id number charge
1 34 0.9
1 56 0.8
1 989 3.8
1 78 1.0
2 76 1.9
2 63 2.8
2 54 0.7
2 45 0.9
3--------
我要选取charge最大的前三条记录,并把charge最大的对应的number记录下来(依次记为number1,number2,number3),形成新表:
id number1 number2 number3
1 989 78 34
2 63 76 45
请问用sql语句怎么做?急问
...全文
22 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
ghtghtmalone 2003-10-20
  • 打赏
  • 举报
回复
先研究一下
newfang 2003-10-20
  • 打赏
  • 举报
回复
select max(fieldname) from tablename into array arrayname
.
.
.
.
也就是把最大的记录赋给数组变量,然后把该记录插入新表,再删除该记录。
把此操作重复三次就可以了!!
daisy0432 2003-10-20
  • 打赏
  • 举报
回复
完全错误,请高手指点
ghtghtmalone 2003-10-20
  • 打赏
  • 举报
回复
我也写了一个存储过程,已经调试通过,不过觉得不太好,效率不高,代码如下:
CREATE TABLE [dbo].[S_tmp] (
[id] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[Number1] [int] NULL ,
[Number2] [int] NULL ,
[Number3] [int] NULL
) ON [PRIMARY]

create table sss
(id varchar(3),
number int,
charge float)

insert into sss
values('1',34,0.9)
insert into sss
values('1',56,0.8)
insert into sss
values('1',989,3.8)
insert into sss
values('1',78,1.0)
insert into sss
values('2',76,1.9)
insert into sss
values('2',63,2.8)
insert into sss
values('2',54,0.7)
insert into sss
values('2',45,0.9)

insert into sss
values('4',71,2.3)
insert into sss
values('4',45,2.1)
insert into sss
values('4',222,0.9)


CREATE PROCEDURE Proc_Convert as
DECLARE @id VARCHAR(6), @tmpid VARCHAR(6),@number int,@number1 int,@number2 int,@number3 int,@charge float,
@Counts int
begin transaction
DECLARE CURSOR_CONVERT CURSOR FOR
SELECT Distinct id from sss order by id desc
OPEN CURSOR_CONVERT
FETCH NEXT FROM CURSOR_CONVERT INTO @id
WHILE @@FETCH_STATUS=0
BEGIN
set @Counts=0
DECLARE CURSOR_CONVERT1 CURSOR FOR
SELECT top 3 * from sss where id=@id order by charge desc
OPEN CURSOR_CONVERT1
if @Counts=0
begin
FETCH NEXT FROM CURSOR_CONVERT1 INTO @tmpid,@number1,@Charge
end
Else If @Counts=1
begin
FETCH NEXT FROM CURSOR_CONVERT1 INTO @tmpid,@number2,@Charge
end
Else if @Counts=2
begin
print @number3
end
set @Counts=@Counts+1

WHILE @@FETCH_STATUS=0
BEGIN
if not exists (select * from s_tmp where id=@id)
insert into s_tmp(id,number1,number2,number3) values(@tmpid,@number1,@number2,@number3 )
else
update s_tmp set number1=@number1,number2=@number2,number3=@number3 where id=@id

FETCH NEXT FROM CURSOR_CONVERT1 INTO @tmpid,@number2,@Charge
FETCH NEXT FROM CURSOR_CONVERT1 INTO @tmpid,@number3,@Charge
END

CLOSE CURSOR_CONVERT1
DEALLOCATE CURSOR_CONVERT1
FETCH NEXT FROM CURSOR_CONVERT INTO @id
END
CLOSE CURSOR_CONVERT
DEALLOCATE CURSOR_CONVERT

commit transaction
GO


exec proc_convert
结果:
id nuber1 number2 number3
1 989 78 34
2 63 76 45
4 71 45 222
好象还有一点问题,就是如果对应某个ID的记录不满3条时,就不准确了。
你自己再调一调吧。
dfwxj 2003-10-20
  • 打赏
  • 举报
回复
这个问题不是简单的一两个命令能解决的,设源表oldtable,为新表为newtable,字段都为数值

clos all
set safe off
set talk off
wait '正在处理数据,请稍候...' wind nowa
crea tabl newtable (id n(4),number1 n(8),number2 n(8),number3 n(8))
sele id dist from oldtable into curs temp1
sele temp1
scan
sele * from oldtable where id=temp1.id into curs temp2 orde by charge desc
sele newtable
appe blan
repl id with temp1.id
sele temp2
go top
do while !eof() and recn()<=3
cnu=allt(str(recn()))
repl number&cnu. with temp2.number in newtable
skip
endd
use in temp2
ends
clos all
wait '数据处理完毕!' wind time 1
set safe off
set talk off
retu


不过楼主一定要用sele语句的话自己改吧~~
gmlwx 2003-10-19
  • 打赏
  • 举报
回复
sele top 3 * from charge order by number1,number2,number3

2,718

社区成员

发帖
与我相关
我的任务
社区描述
VFP,是Microsoft公司推出的数据库开发软件,用它来开发数据库,既简单又方便。
社区管理员
  • VFP社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧