一些实用SQL语句

浏览数：47 / 时间：2015年06月12日

1. 取10--20条记录

select news_id from Item_T_Info_News 
where news_id>=(select  max(t1.News_ID) from (select top 10 * from Item_T_Info_News order by News_ID) as t1) 
and news_id<=(select  max(t2.News_ID) from (select top 20 * from Item_T_Info_News order by News_ID) as t2)

2. 表变量和临时表的定义

表变量

DECLARE @indextable table
(
    uid int identity(1,1),    
    id int
)

临时表

CREATE Table #temptable
(
    id int,
    date datetime        
)

当数据量大时,建议使用临时表!

3. 错开排序

declare @t table(empid int,empname varchar(10),deptid int)
insert into @t select 1,‘员工A‘,5
insert into @t select 2,‘员工B‘,5
insert into @t select 3,‘员工C‘,7
insert into @t select 4,‘员工D‘,7
insert into @t select 5,‘员工E‘,9
insert into @t select 6,‘员工F‘,5
insert into @t select 7,‘员工G‘,7
insert into @t select 8,‘员工H‘,9
insert into @t select 9,‘员工I‘,3

select px=(select count(1) from @t where deptid=a.deptid and empid<=a.empid),* from @t a
order by px,deptid

4. 统计某一产品出现的次数

declare @myTable table
(
　　pro_ID int,
　　pro_count int
)
insert into @myTable(pro_ID,pro_count) 
select OrderPro_ProID,count(OrderPro_ProID) from Item_T_Order_OrderPro
group by OrderPro_ProID
select * from @myTable

5. 按in的顺序排序

select * from Item_T_Pro_Product 
where Pro_ID in(90,153,158,80,76,98,96,85,101,104)
ORDER BY 
charindex(‘,‘+cast(Pro_ID as varchar(10))+‘,‘, ‘,90,153,158,80,76,98,96,85,101,104,‘)

6. split函数

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[split]
 (@str nvarchar(4000),@code varchar(10),@no int )  
RETURNS varchar(200)
AS  
BEGIN
declare @intLen int
declare @count int
declare @indexb  int
declare @indexe  int
set @intLen=len(@code)
set @count=0
set @indexb=1

if @no=0
  if charindex(@code,@str,@indexb)<>0
     return left(@str,charindex(@code,@str,@indexb)-1) 
  else
     return @str
while charindex(@code,@str,@indexb)<>0
  begin
       set @count=@count+1
       if @count=@no
         break
       set @indexb=@intLen+charindex(@code,@str,@indexb)
  end

if @count=@no
begin
      set @indexe=@intLen+charindex(@code,@str,@indexb)
          if charindex(@code,@str,@indexe)<>0
             return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code))
          else 
             return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1)
  end
return ‘‘
END

Code

7. 清空数据库日志文件，并压缩大小为1M

DUMP TRANSACTION Holpe_Order WITH NO_LOG
BACKUP LOG Holpe_Order  WITH NO_LOG 
DBCC SHRINKDATABASE(Holpe_Order)

8. sql语句备份与还原数据库

USE master
RESTORE FILELISTONLY
FROM DISK = ‘g:\back.Bak‘
Go
************************************************
/*
 利用bak恢复数据库，强制还原(REPLACE)
 STATS = 10 每完成10%显示一条记录
 DBTest和DBTest_log是上面g:\back.Bak里的逻辑文件
 */
USE master
RESTORE DATABASE DB
FROM DISK = ‘g:\back.Bak‘
WITH MOVE ‘DBTest‘ TO ‘E:\Program Files\Microsoft SQL Server2005\Data\DB.mdf‘,
MOVE ‘DBTest_log‘ TO ‘E:\Program Files\Microsoft SQL Server2005\Data\DB_log.ldf‘,
STATS = 10, REPLACE
GO
++++++++++++++++++++++++++++++++

/*
 备份数据DB 到.bak文件。然后利用此bak文件恢复一个新的数据库DBTest。
 */
USE master
BACKUP DATABASE DB
TO DISK = ‘g:\DBBack0930.bak‘
RESTORE FILELISTONLY
FROM DISK = ‘g:\DBBack0930.bak‘
RESTORE DATABASE DBTest
FROM DISK = ‘g:\DBBack0930.bak‘
WITH MOVE ‘DBTest‘ TO ‘E:\Program Files\Microsoft SQL Server2005\Data\DBTest.mdf‘,
MOVE ‘DBTest_log‘ TO ‘E:\Program Files\Microsoft SQL Server2005\Data\DBTest_log.ldf‘
GO

Code

9. 通用分页存储过程

Create PROCEDURE [dbo].[usp_GetRecordFromPage]
    @tblName       varchar(1000),        -- 表名
    @SelectFieldName    varchar(4000),              -- 要显示的字段名(不要加select)
    @strWhere       varchar(4000),              -- 查询条件(注意: 不要加 where)
    @OrderFieldName      varchar(255),               -- 排序索引字段名
    @PageSize       int ,                 -- 页大小
    @PageIndex      int = 1,                  -- 页码
    @iRowCount      int output,                 -- 返回记录总数
    @OrderType      bit = 0                  -- 设置排序类型, 非 0 值则降序
           
AS
declare @strSQL    varchar(4000)       -- 主语句
declare @strTmp    varchar(4000)        -- 临时变量
declare @strOrder  varchar(400)        -- 排序类型
declare @strRowCount    nvarchar(4000)      -- 用于查询记录总数的语句
set @OrderFieldName=ltrim(rtrim(@OrderFieldName))
if @OrderType != 0
begin
    set @strTmp = ‘<(select min‘
    set @strOrder = ‘ order by ‘ + @OrderFieldName +‘ desc‘
end
else
begin
    set @strTmp = ‘>(select max‘
    set @strOrder = ‘ order by ‘ + @OrderFieldName +‘ asc‘
end
set @strSQL = ‘select top ‘ + str(@PageSize) + @SelectFieldName+‘ from ‘
    + @tblName + ‘ where ‘ + @OrderFieldName + @strTmp + ‘(‘
    + right(@OrderFieldName,len(@OrderFieldName)-charindex(‘.‘,@OrderFieldName)) + ‘) from (select top ‘ + str((@PageIndex-1)*@PageSize)
    + @OrderFieldName + ‘ from ‘ + @tblName  + @strOrder + ‘) as tblTmp)‘
    + @strOrder
if @strWhere != ‘‘
    set @strSQL = ‘select top ‘ + str(@PageSize) + @SelectFieldName+‘ from ‘
        + @tblName + ‘ where ‘ + @OrderFieldName + @strTmp + ‘(‘
        + right(@OrderFieldName,len(@OrderFieldName)-charindex(‘.‘,@OrderFieldName)) + ‘) from (select top ‘ + str((@PageIndex-1)*@PageSize)
        + @OrderFieldName + ‘ from ‘ + @tblName + ‘ where ‘ + @strWhere + ‘ ‘
        + @strOrder + ‘) as tblTmp) and ‘ + @strWhere + ‘ ‘ + @strOrder
if @PageIndex = 1
begin
    set @strTmp = ‘‘
    if @strWhere != ‘‘
        set @strTmp = ‘ where ‘ + @strWhere
    set @strSQL = ‘select top ‘ + str(@PageSize) + @SelectFieldName+‘ from ‘
        + @tblName + @strTmp + ‘ ‘ + @strOrder
end
exec(@strSQL)
 
if @strWhere!=‘‘
begin
  set @strRowCount = ‘select @iRowCount=count(*) from ‘ + @tblName+‘ where ‘+@strWhere
end
else
begin
  set @strRowCount = ‘select @iRowCount=count(*) from ‘ + @tblName
end
exec sp_executesql @strRowCount,N‘@iRowCount int out‘,@iRowCount out