create proc p_qry
@n int=5,
@m int=7
as
select *
from [table]
where (
select count(distinct score) from [table]
where score>=a.score
)between @n and @m
@n int=5,
@m int=7
as
select *
from [table]
where (
select count(distinct score) from [table]
where score>=a.score
)between @n and @m
解决方案 »
- 这样的排序逻辑该怎么写?
- 求一条SQL语句或者写法,字段中一部分数字重新编号问题
- 建了索引后,select速度好像没比较快?
- 通过adsl上网为什么不能注册到sqlserver服务器??
- 用SQL2008语句如何将EXCEL导入SQL中~~~~~~~~~~~~~~~~~~~~急!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 同一列,时间差计算
- 关于查询和索引和CPU
- 存储过程中变量放在SQL语句中得不到值
- SQL SERVER 读取数据的问题?
- 用VB6 + ADO + SQL SERVER 2000 怎样来实现这样的功能 ?
- 求助:如何进行以下方式的数据格式变换(急!)
- 求助:如何判断gz_tab是否存在?
--存储过程少写了别名
create proc p_qry
@n int=5,
@m int=7
as
select *
from [table] a
where (
select count(distinct score) from [table]
where score>=a.score
)between @n and @m
create table [table](id int,score int)
insert [table] select top 20 id,colid
from syscolumns
order by newid()
go--存储过程
create proc p_qry
@n int=5,
@m int=7
as
select *
from [table] a
where (
select count(distinct score) from [table]
where score>=a.score
)between @n and @m
go--显示原表数据
select * from [table] order by score desc--调用存储过程进行查询
exec p_qry
go--删除测试
drop table [table]
drop proc p_qry/*--测试结果id score
----------- -----------
2 29
3 25
14 21
14 16
10 13
4 12
12 9
4 6
261575970 6
1 6
261575970 5
1975678086 4
277576027 3
379148396 3
10 3
261575970 2
229575856 2
19 2
9 1
23 1(所影响的行数为 20 行)id score
----------- -----------
12 9
4 12
10 13(所影响的行数为 3 行)--*/
----------- -----------
1662628966 15
40 15
41 14
859866130 10
31 5
2095346529 4
456388695 4
206623779 3
33 3
788913882 3
38 2
1746105261 2
162815642 2
1588916732 2
463340715 2
1977058079 2
1563868638 1
1733581214 1
1700201107 1
326292222 1得到的结果竟然是:
----------- -----------
456388695 4
206623779 3
463340715 2
1977058079 2
33 3
1588916732 2
38 2
1746105261 2
162815642 2
2095346529 4
788913882 3显然是 count(distinct score) 造成的。
执行存储过程显示:
id score
----------- -----------
1060198827 4
1303675692 2
1664724983 2
1741249258 2
1410104064 2
925246351 2
944722418 3
846626059 2
308196148 2
898102240 2
503672842 4
288720081 2(所影响的行数为 12 行)
是不是搞错了.......
列出前七个中分数最小的最后两个select top 7-5 * from table
where 主码 in (select top 7 主码 from table order by 分数)
order by 分数 desc把上边的7和5换为参数的sql @max,@min
select top @max-@min * from table
where 主码 in (select top @max 主码 from table order by 分数)
order by 分数 desc写为存储过程就是
create proc score
@max int,
@min int
as
declare @sql varchar(1000)set @sql='select top @max-@min * from 表 where 主码 in (select top @max 主码 from 表 order by 分数) order by 分数 desc'
GO-------
大概思路如此,没有调试过
也许没有zjcxc(邹建) 的方法好
@n int=5,
@m int=7
as
select *
from [table] a
where (select count(distinct score) from [table] where score>=a.score) between @n and @m唉,不懂就是不懂啊郁闷ing
ilittlefat(已瘦很多了!) ( ) 信誉:100
两位的方法都正确
更正一下
set @sql='select top @max-@min * from 表 where 主码 in (select top @max 主码 from 表 order by 分数) order by 分数 desc'
改为
set @sql='select top '+@max-@min+' * from 表 where 主码 in (select top @max 主码 from 表 order by 分数) order by 分数 desc'
呵呵
set @sql='select top '+@max-@min+' * from 表 where 主码 in (select top '+@max+'主码 from 表 order by 分数) order by 分数 desc'
from [table] a --外查询
where
(
select count(distinct score) from [table] where score>=a.score --内查询
) between @n and @mwhere 里面这句(内查询)是查出分数大于当前学生分数(也就是外查询)的条数,
然后在按照这个数据进行排序查找
where (select count(distinct score) from [table] where score>=a.score) between @n and @m
好吗?
(select top 5 * from scoreTable order by score)A
order by score desc
@N int,@M int
as
begin
select top (@M-@N+1) * from (
select top ((select count(*) from table1)-@N)from tabel1 order by score desc) order by score asc
end
create proc score
@max int,
@min int
as
declare @sql varchar(1000)set @sql='select top @max-@min * from 表 where 主码 in (select top @max 主码 from 表 order by 分数) order by 分数 desc'
exec @sql
GO
@n int,
@m int
as
select *
from [table] a
where (
select count(distinct score) from [table]
where score>=a.score
)between @n and @m order by score desc
go是不是加一个order by 比较明白
@n int=5,
@m int=7
as
select *
from [table] a
where (select count(distinct score) from [table] where score>=a.score)
between @n and @m order by score desc
我也不太了解请谁指点一下
@n int=5,
@m int=7
as
select top m-n+1 *
from [table]
where id not in (select top n-1 id from [table] order by score desc)
order by score desc
@n int=5,
@m int=7
SELECT (SELECT COUNT(DISTINCT a)
FROM table1 a
WHERE a.score >= b.score) AS no, *
into #Temp
FROM table1 b
ORDER BY no
select * from #Temp where no between @n and @m
drop table #Temp