纳金网

标题: 常用sql笔记 [打印本页]

作者: may 时间: 2016-1-16 03:37
标题: 常用sql笔记
1、查询锁住的表，并删除
select
request_session_id spid ,
OBJECT_NAME(resource_associated_entity_id ) tableName
from
sys.dm_tran_locks
where
resource_type='OBJECT'
KILL 107--spid

2、跨数据库，将表A插到表B
SET IDENTITY_INSERT [dbo]. T_Bas_Enterprise ON
INSERT INTO dbo. T_Bas_Enterprise
      ( PK_UCode ,
      PK_EntID,
      EntCode ,
      UnitName ,
      ShortName ,
      UCPinyin ,
      Leager ,
      LeagerPhone ,
      Licence ,
      FK_EntTypeID ,
      FK_CTCode ,
      FK_VTCode ,
      FK_ESCode ,
      FK_RTCode ,
      FK_ERCode ,
      LinkName ,
      LinkPhone ,
      LinkMPhone ,
      LinkMail ,
      Fax ,
      PostCode ,
      PPDate ,
      Address ,
      EntPic ,
      PosX ,
      PosY ,
      fbitIsAnnex ,
      fbitStop ,
      CreateDate ,
      Creater ,
      UpdateDate ,
      Updater
      )
SELECT
   NEWID() PK_UCode,PK_EntID ,EntCode, UnitName,ShortName ,UCPinyin, Legaler,BPhone LeagerPhone,NULL Licence,
   NULL fk_entTypeID,FK_CTCode ,FK_VTCode, FK_ESCode,FK_RTCode ,FK_ERCode ,
   LinkName,Phone linkphone, Mobile linkmphone ,EMail linkMail,Fax ,PostCode, PPDate,Address ,
   EntPic,PosX ,PosY, fbitIsAnnex,fbitStop ,fdtmCreateDate CreateDate,fdtmCreateUser Creater,
   fdtmCreateDate updateteDate ,fdtmCreateUser updater
FROM [192.168.253.63] .[DB_JJDC]. [CSBAS].[T_Bas_Enterprise]

3、当前数据库中，将表A插入到表B
insert into a (a的字段 ) select * form b
a的字段与*字段要一致

4、将List 以指定字符连接成一个字符串  如  1、2、 3
List<string> list = new List<string>();
         var str = list.Aggregate((p, next) => (p + "-" + next));

5、将某个字段  转成对应的类型
cast( unit.PK_EntID as nvarchar(50)) PK_EntID

6、存储过程分页

USE 数据库名
GO
/****** Object:  StoredProcedure [dbo].[fw_PageProcedure] Script Date: 12/29/2015 10:18:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[fw_PageProcedure]
@outRecordCount                                  bigint output,                --返回记录总数，好进行分页
@outPageSize                                     bigint output,                      --每页显示的记录数
@outPageIndex                                     bigint output,                      --当前页数

@pageSize                                     bigint,                      --每页显示的记录数
@pageIndex                                     bigint,                      --当前页数
@searchField                                  varchar(8000),                --所要查询的字段的集合字符串
@afterFromBeforeOrderBySqlString             varchar(8000),             --SQL语句第一个from 到最后一个 order 之间字符串
@orderByField                               varchar(200)                --最后一个order by 后面语句最后的字符串

as

--计算所有的记录条数并返回
declare @@sqlCount nvarchar(max)
set  @@sqlCount=N' select @outRecordCount=COUNT(*) '+N' from '+ @afterFromBeforeOrderBySqlString
exec sp_executesql @@sqlCount , N'@outRecordCount int output' , @outRecordCount output

--定义指定页需要查看的记录条数
if @pageIndex<=1          --优化第一页的查询，直接获得前@pageSize条记录
begin
set @pageIndex=1
-- exec('  select top '+@pageSize+' '+@searchField+'
--from '+@afterFromBeforeOrderBySqlString+'
--order by '+@orderByField)

declare @@sql  varchar(max)
set @@sql= '  select top '+ltrim
      (@pageSize)+' '+@searchField+'
from '+@afterFromBeforeOrderBySqlString+'
order by '+@orderByField
      exec(@@sql)
print  @@sql
end

else
begin
      declare @@PageCount bigint
      declare @@IndexFrom bigint
      declare @@IndexTo bigint
      set @@PageCount=@outRecordCount/@pageSize
      if(@outRecordCount%@pageSize>0)
         begin
            set @@PageCount=@@PageCount+1
         end
      if(@pageIndex>=@@PageCount)
         begin
         set @pageIndex=@@PageCount
         set @@IndexFrom=(@pageIndex-1)*@pageSize+1
         set @@IndexTo =@outRecordCount
         end
      else
         begin
         set @@IndexFrom=(@pageIndex-1)*@pageSize+1
         set @@IndexTo =@pageIndex*@pageSize
         end

exec('  select
*
from
(select
ROW_NUMBER() OVER(ORDER BY '+@orderByField+' ) PageProcedureRowNumber,'+@searchField+'
from
'+@afterFromBeforeOrderBySqlString+') PageProcedureTable
where
PageProcedureTable.PageProcedureRowNumber between '+@@IndexFrom+' and '+@@IndexTo)

end

set @outPageSize=@pageSize
set @outPageIndex=@pageIndex

6、将日期的年月提取出来，进行处理
select place,count(*) Value ,CONVERT(VARCHAR(7),happenTime,120) YearAndMonth  from T_Dat_UnexpectedEvent  unex
                                       where place is not null
                                       and happenTime between '2009-04-23 09:15:00.000' and  '2015-12-23 00:00:00.000'
                                       and place in(1,2)
                                       group by  place, CONVERT(VARCHAR(7),happenTime,120)
                                       order by CONVERT(VARCHAR(7),happenTime,120)

欢迎光临纳金网 (http://go.narkii.com/club/)