成绩表
编号 英语 名次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怎样获得可执行文件所在的路径?
- 请教一个RptFunction控件的问题?
- 如何彻底的释放串口
- inet_ntoa函數的宣告及使用方法
- 如何把窗体置于顶层
- 请问command1_click 事件中 adobc1.recordset.movenext 到最后一条的下一条时会出错,要如何写代码
- 我发现使用unload me 然后再set form1=nothing后系统会为form1重新创建,并执行init.
- 关于vb控件传递字符串给pb出现的问题。。。。。在线等待
- 如何制作永久显血外挂
- 泰山是这样的~~~~~~~~~~~~~~~~~~~~~~~程序已经全部完工
- 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