declare @t table(名称 varchar(10),分数 int)
insert into @t select '王明',100
union all select '小张',90select [名次]=identity(int,1,1),* into # from @t order by 分数 desc
select * from #
drop table #
insert into @t select '王明',100
union all select '小张',90select [名次]=identity(int,1,1),* into # from @t order by 分数 desc
select * from #
drop table #
where 分数<=a.分数),名称,分数
from table1 as a
insert @t
select '王明',100 union all
select '小张',90
select 名次=(select count(1) from @T where 分数>=t.分数),名称, 分数 from @T t
where 分数>a.分数)+1,名称,分数
from table1 as a
有一个表A,它有一个varchar(10)的字段,num; 内容如下:
A
001
002
003 //注意“004”,“005”没有。。
006
007
......
要求,能够把“004”给查出来。
如果把“004”插入后,则把“005”查询出来。
如果把“004”,“005”都插入后,那么应该把“008”查询出来。
*/--YCZEALOT 20060816IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID=OBJECT_ID('T') AND XTYPE='U') DROP TABLE T
CREATE TABLE T
(
V CHAR(8)
)INSERT INTO T(v)
SELECT '0001' UNION ALL
SELECT '0002' UNION ALL
SELECT '0004' UNION ALL
SELECT '0006' UNION ALL
SELECT '0007' UNION ALL
SELECT '0009' IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID=OBJECT_ID('TEMPDB..#T') AND XTYPE='U') DROP TABLE #T
SELECT ID1=IDENTITY(INT,1,1),* INTO #T FROM T
SELECT TOP 1 CASE (LEN(CONVERT(INT,V))) WHEN 1 THEN '000'
WHEN 2 THEN '00'
WHEN 3 THEN '0'
ELSE ''
END
+CONVERT(CHAR(1),ID1) AS V FROM #T WHERE ID1<>V/*
--RESULT
--0003
*/
关于分数有无重复值的查询都有详细说明.主要是利用count生成序数.