SQL Select N to M Records (single Table)
取表里n到m条纪录的几种方法:
1. 只需要查询前M条数据(0 to M),
1.1 使用 top(M) 方法:
select top(3) * from [tablename]
1.2 使用 set rowcount 方法:
http://msdn.microsoft.com/zh-cn/library/ms188774(SQL.90).aspx
set rowcount M
select * from [tablename]
set rowcount 0
权限 要求具有 public 角色成员资格。
要执行set rowcount 0, 否则影响以后查询等.
2.查询N到M条数据(N to M),
2.1 表里面有标识列
2.1.1
select top (M-N+1) * from [tablename] where [columnname] not in (select top (N) [columnname] from [tablename])
2.1.2 逆序显示
select top N * from (select top M * from [tablename] order by [columnname]) temp order by [columnname] desc
2.1.3 顺序显示
select * from (select top N * from (select top M * from [tablename] order by [columnname]) temp1 order by [columnname] desc) temp2 order by [columnname]
2.2 表里有identity属性
select * from [tablename] where identitycol between N and M
如[columnname]为identity属性,则可以写成:
select * from [tablename] where [columnname] between N and M
2.3 表里面有标识列, 利用临时表
IF Exists(Select 1 from sysObjects Where Name ='temptable' And Type In ('temptable','U'))
begin
drop table [temptable]
end
select top M * into [temptable] from [tablename] order by [columnname]
set rowcount N
select * from [temptable] order by [columnname] desc
set rowcount 0
drop table [temptable]
2.4 如果tablename里没有其他identity列,那么:
exec sp_dboption [DataBaseName] ,'select into/bulkcopy',true
IF Exists(Select 1 from sysObjects Where Name ='temptable' And Type In ('temptable','U'))
begin
drop table temptable
end
select identity(int) id0,* into [temptable] from [tablename]
select * from temp where id0 >= N and id0 <= M
drop table temptable
如果你在执行select identity(int) id0,* into [temp
相关文档:
SQL的优化应该从
5
个方面进行调整:
1.去掉不必要的大型表的全表扫描
2.缓存小型表的全表扫描
3.检验优化索引的使用
4.检验优化的连接技术
5.尽可能减少执行计划的
Cost
SQL语句:
是对数据库(
数据
)
进行操作的惟一途径;
消耗了70%~90%
的数据库资源;独立于程序设计逻辑,相对于对程序源代码的优化, ......
I'm continually trying to track down what service packs are installed on various SQL Servers I support. I can never find the right support page on Microsoft's site. So here's an article with all the SQL Server version information I can track down. If you know of any older versions or can help me fil ......
本文主要内容属转载,但笔者根据该文内容测试成功,故分享于此。
笔者实验环境:Windows Server 2003 Enterprise Edition。
先给出原文链接,稍后调整。
原文链接为:http://www.shilai.cn/2007/5/6/problems-of-installing-sql2005.aspx ......
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符,返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
......
1,SELECT *,CASE OrderStatus WHEN 1 THEN '未处理' when 2 THEN '锁定' when
3 THEN '已出票' ELSE '过期' END
from dbo.T_OrderItem
2,
SELECT *,CAST(ROUND(CAST (HostWin AS FLOAT)/(HostWin+HostDraw+HostBear),2)*100 AS varchar)+'%' AS HostWinRate,
& ......