select * from tb order by djbh , sno ?
解决方案 »
- 用脚本分离数据库时怎么关强制闭现有的用户进程
- 请问在SQL SERVER 中 CASE 语句里面可以写 查询语句嘛?
- 如何在求平均值时,值为零时值不统计?
- 同时更新多行,但每行的每列值必须更新不一样
- 使用sspi连接方式时如何设置sql server的用户权限啊?
- sql 到 access
- EXEC master..xp_cmdshell 'bcp master.dbo.sysobjects out c:\temp1.xls -c -S"oa" -U"sa" -P""' 出错如何处理?
- 存储过程的返回值问题
- MS-SQL的一个小小问题???(不太好意思拿来此提问,嘻嘻)
- 请问用ado可以做数据库备份和恢复方面的编程么?
- SQL 数据库文件超级大,而且增长速度过快!
- 这条sql语句怎么改写,能提高性能!!!!!!!!!!!!!!!!!!!!!
select djbh , mxdh , sno = (select count(1) from tb where djbh = t.djbh and mxdh > t.mxdh) from tb t
create table tb(djbh varchar(20), mxdh varchar(20), sno int)
insert into tb values('060011211600' , '26' , 3 )
insert into tb values('060011211600' , '25' , 2 )
insert into tb values('060011211600' , '22' , 1 )
insert into tb values('060011211600' , '02' , 5 )
insert into tb values('060011211600' , '01' , 4 )
insert into tb values('060031211500' , '26' , 3 )
insert into tb values('060031211500' , '25' , 2 )
insert into tb values('060031211500' , '22' , 1 )
insert into tb values('060031211500' , '03' , 6 )
insert into tb values('060031211500' , '02' , 5 )
insert into tb values('060031211500' , '01' , 4 )
goselect djbh , mxdh , sno = (select count(1) from tb where djbh = t.djbh and mxdh > t.mxdh) + 1 from tb t order by djbh , snodrop table tb/*
djbh mxdh sno
-------------------- -------------------- -----------
060011211600 26 1
060011211600 25 2
060011211600 22 3
060011211600 02 4
060011211600 01 5
060031211500 26 1
060031211500 25 2
060031211500 22 3
060031211500 03 4
060031211500 02 5
060031211500 01 6(所影响的行数为 11 行)
*/
-------------------------------- -------------------------------- -----------
insert @t select '060011211600' , '26' , 3
insert @t select '060011211600' , '25' , 2
insert @t select '060011211600' , '22' , 1
insert @t select '060011211600' , '02' , 5
insert @t select '060011211600' , '01' , 4
insert @t select '060031211500' , '26' , 3
insert @t select '060031211500' , '25' , 2
insert @t select '060031211500' , '22', 1
insert @t select '060031211500' , '03', 6
insert @t select '060031211500' , '02', 5
insert @t select '060031211500' , '01', 4
select djbh ,mxdh,(select count(*)+1 from @t t1 where t1.djbh=t2.djbh and t1.mxdh >t2.mxdh )as 排名 from @t t2 order by djbh ,mxdh desc
/*
djbh mxdh 排名
--------------- ---------- -----------
060011211600 26 1
060011211600 25 2
060011211600 22 3
060011211600 02 4
060011211600 01 5
060031211500 26 1
060031211500 25 2
060031211500 22 3
060031211500 03 4
060031211500 02 5
060031211500 01 6*/
djbh mxdh sno
-------------------- -------------------- -----------
060011211600 26 1
060011211600 25 2
060011211600 22 3
060011211600 02 4
060011211600 01 5
060031211500 26 1
060031211500 25 2
060031211500 22 3
060031211500 03 4
060031211500 02 5
060031211500 01 6(所影响的行数为 11 行)
*/