成绩表
编号 英语 名次1 数学 名次2 计算机 名次3...
1001 89 87 99
1002 90 78 98
1003 90 88 95
1004 88 90 93以下这句会查询出各科名次,但我想把它们存入数据表中的相应名次字段,该怎么实现呢?SELECT 编号 ,
(select count(*)+1 from 成绩 as t2 where t2.英语>t1.英语) AS 名次1 ,英语,
(select count(*)+1 from 成绩 as t2 where t2.数学>t1.数学) AS 名次2 ,数学,
(select count(*)+1 from 成绩 as t2 where t2.数学>t1.计算机) AS 名次3 ,计算机
FROM 成绩 AS t1
编号 英语 名次1 数学 名次2 计算机 名次3...
1001 89 87 99
1002 90 78 98
1003 90 88 95
1004 88 90 93以下这句会查询出各科名次,但我想把它们存入数据表中的相应名次字段,该怎么实现呢?SELECT 编号 ,
(select count(*)+1 from 成绩 as t2 where t2.英语>t1.英语) AS 名次1 ,英语,
(select count(*)+1 from 成绩 as t2 where t2.数学>t1.数学) AS 名次2 ,数学,
(select count(*)+1 from 成绩 as t2 where t2.数学>t1.计算机) AS 名次3 ,计算机
FROM 成绩 AS t1
解决方案 »
- 用vb如何在当前DOC新建多页或一页谢谢!!!
- 提前散分
- F1Book合并问题?急!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- vb的递归问题,帮忙看看算法
- 请问“文件名、目录名或卷标名不正确”是什么错误?具体情况请进来看看
- 在VB里面控制WebBrowser的问题,(内详)有请playuer、小草、actpvb等高手看看,急!!!
- 有没有类似于win2000的tcp/ip地址设置当中的Ip地址输入框的控件?
- 请大家介绍一下学习VB的好书?(提高用的)
- 请问如何编写像”一拖即存“这个软件上的拖放框
- 关于VB窗体接收自定义消息和AddressOf 操作的疑惑
- update语法错误,大家看看什么问题啊
- 请教游戏里坐标的获取问题
update 成绩 t1 set t1.名次1=(select count(*)+1 from 成绩 as t2 where t2.英语>t1.英语)
update 成绩 t1 set t1.名次1=(select count(*)+1 from 成绩 as t2 where t2.数学>t1.数学)
update 成绩 t1 set t1.名次1=(select count(*)+1 from 成绩 as t2 where t2.计算机>t1.计算机)
--其它你的SQL查询都写出来了,那么更新也就很简单了,看下面的测试代码--编号 英语 名次1 数学 名次2 计算机 名次3...
--1001 89 87 99
--1002 90 78 98
--1003 90 88 95
--1004 88 90 93 create table Score (
sNo varchar(6)
, English int
, Rank1 int null
, Math int
, rank2 int null
, Computer int
, Rank3 int null
)insert into score
values ( '1001', 89, null, 87, null, 99, null )
insert into score
values ( '1002', 90, null, 78, null, 98, null )
insert into score
values ( '1003', 90, null, 88, null, 95, null )
insert into score
values ( '1004', 88, null, 90, null, 93, null )
SELECT sNo
, ( select count(*) + 1
from Score as t2
where t2.English > t1.English
) AS Rank1
, English
, ( select count(*) + 1
from Score as t2
where t2.Math > t1.Math
) AS rank2
, Math
, ( select count(*) + 1
from Score as t2
where t2.Math > t1.Computer
) AS Rank3
, Computer
FROM Score AS t1 update Score
set rank1 = Rank1_u
, rank2 = Rank2_u
, rank3 = Rank3_u
from Score
, ( SELECT sNo
, ( select count(*) + 1
from Score as t2
where t2.English > t1.English
) AS Rank1_u
, ( select count(*) + 1
from Score as t2
where t2.Math > t1.Math
) AS rank2_u
, ( select count(*) + 1
from Score as t2
where t2.Math > t1.Computer
) AS Rank3_u
FROM Score AS t1
) Ta
where score.sNo = Ta.Snoselect *
from Scoredrop table Score