1:
将
select top 100 *,isnull [select sum(1) from gh_jgpb where zf>a.zf],0)+(as '名次' from gh_jgpb A order by zf desc
的查询结果给一别名B
2:
select * from (select top 100 *,isnull [select sum(1) from gh_jgpb where zf>a.zf],0)+(as '名次' from gh_jgpb A order by zf desc) as B where tbrq='2003-06' order by xh
相当于
select * from B where tbrq = '2003-06' order by xh
将
select top 100 *,isnull [select sum(1) from gh_jgpb where zf>a.zf],0)+(as '名次' from gh_jgpb A order by zf desc
的查询结果给一别名B
2:
select * from (select top 100 *,isnull [select sum(1) from gh_jgpb where zf>a.zf],0)+(as '名次' from gh_jgpb A order by zf desc) as B where tbrq='2003-06' order by xh
相当于
select * from B where tbrq = '2003-06' order by xh
来构成B表﹐然后根据条件{ select * from (构成的B表) where tbrq='2003-06'
order by xh}从B表中刷选出资料。
从gh_jgpb表中选出tbrq(填报日期)是2003年06月的zf(总分)前100名,并追加名次,然后按xh(学号)排序的记录集,
应该是学籍管理的SQL吧,大概是老师布置的作业,^O^select * from (select top 100 *,isnull [select sum(1) from gh_jgpb where zf>a.zf],0)+(as '名次' from gh_jgpb A order by zf desc) as B where tbrq='2003-06' order by xh 中间存在几处问题[]在SQL SERVER中事实上是转义或者长字段名使用的,中间的select语句不会起任何作用
[]中的select有zf>a.zf条件,而from后的表名没定义任何别名,应该会报错,
[]中的意思大概是以某条记录的zf(总分)为条件,筛选并统计高于此分数的记录,作为名次号,这是不可能的,因为SQL语句不支持着这方式
sum(1)效果等同于count(*),建议用count(*),一般情况下平均快7.23%
as '名次'是做[]中列的别名吧?不清楚前面的"+("是什么意思
tbrq应该是时间型吧,用"="会只筛选出2003-06-01 00:00:00的记录,建议用year()=和month()=来条件筛选,当然,如果正表数据大于4000行,这样就会影响速度,可改用between '2003-06-01 00:00:00' and '2003-06-30 23:59:59'来筛选
isnull最好用()括起来,方便阅读
SQL语句最好用回车和缩紧来规范格式,明晰定义,这样在查询分析器里也好找错误
例如:
select
*
from
(
select top 100
*,
isnull (select sum(1) from gh_jgpb where zf>a.zf,0)
as '名次'
from gh_jgpb A
order by zf desc
) as B
where tbrq='2003-06'
order by xh不过申明,这条语句执行不通的.
佩服,解释的非常详细,这条语句确实执行不通的,谢谢!!