2007年11月15日
#
/*
---查询SQL是否进程死锁
exec ljj_SP_Sys_LockInfo
@kill_lock_spid=0,
@show_spid_if_nolock=1
*/
ALTER proc dbo.ljj_SP_Sys_LockInfo
@kill_lock_spid bit=0, --是否殺掉鎖死的進程,1 殺掉, 0 僅顯示
@show_spid_if_nolock bit=1 --如果沒有鎖死的進程,是否顯示正常進程資訊,1 顯示,0 不顯示
as
declare @count int,@s nvarchar(4000),@i int
select id=identity(int,1,1),標誌,
進程ID=spid,線程ID=kpid,塊進程ID=blocked,資料庫ID=dbid,
資料庫名=db_name(dbid),用戶ID=uid,用戶名=loginame,累計CPU時間=cpu,
登陸時間=login_time,打開事務數=open_tran, 進程狀態=status,
工作站名=hostname,應用程式名=program_name,工作站進程ID=hostprocess,
功能變數名稱=nt_domain,網卡位址=net_address
into #t from(
select 標誌='鎖死的進程',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|_犧牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1
begin
insert #t
select 標誌='正常的進程',
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
from master..sysprocesses
set @count=@@rowcount
end
if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(3500))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@標誌 varchar(10)
while @i<=@count
begin
select @spid=進程ID,@標誌=標誌 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @標誌='鎖死的進程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(進程ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,進程的SQL語句=b.EventInfo,b.a
from #t a join #t1 b on a.id=b.id
order by a.進程ID
end
/*
---游標處理批量流程---------
---步驟1,建立表--檢查ProjectCode是否帶有特殊字符
---步驟2---檢查所有簽核人帳號是否存在---
--檢查導入簽核人是否正確（主要判斷數據是否為NULL）
select distinct [級數] from dbo.[zlq_PCO1107]
select * from dbo.[zlq_PCO1107] where [級數]=1 and ([一級簽核] is NULL)
select * from dbo.[zlq_PCO1107] where [級數]=2 and ([二級簽核] is NULL or [一級簽核] is NULL)
select * from dbo.[zlq_PCO1107] where [級數]=3 and ([三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select * from dbo.[zlq_PCO1107] where [級數]=4 and ([四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select * from dbo.[zlq_PCO1107] where [級數]=5 and ([五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select * from dbo.[zlq_PCO1107] where [級數]=6 and ([六級簽核] is NULL or [五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select * from dbo.[zlq_PCO1107] where [級數]=7 and ([七級簽核] is NULL or [六級簽核] is NULL or [五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
select * from dbo.[zlq_PCO1107] where [級數]=8 and ([八級簽核] is NULL or [七級簽核] is NULL or [六級簽核] is NULL or [五級簽核] is NULL or [四級簽核] is NULL or [三級簽核] is NULL or [二級簽核] is NULL or [一級簽核] is NULL)
---檢查是否存在于表dbo.PCO_CheckMan中-----
----首先帳號是否存在于表dbo.PCO_CheckMan中---
----其次真實帳號是否存在于表dbo.Ac_BRM_User中---
select a.[一級簽核],b.Username,c.CheckManID from (select distinct [一級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User b on b.realname=a.[一級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[二級簽核],b.Username,c.CheckManID from (select distinct [二級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User b on b.realname=a.[二級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[三級簽核],b.Username,c.CheckManID from (select distinct [三級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User b on b.realname=a.[三級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[四級簽核],b.Username,c.CheckManID from (select distinct [四級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User b on b.realname=a.[四級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[五級簽核],b.Username,c.CheckManID from (select distinct [五級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User b on b.realname=a.[五級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[六級簽核],b.Username,c.CheckManID from (select distinct [六級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User b on b.realname=a.[六級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[七級簽核],b.Username,c.CheckManID from (select distinct [七級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User b on b.realname=a.[七級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
select a.[八級簽核],b.Username,c.CheckManID from (select distinct [八級簽核] from dbo.[zlq_PCO1107]) a left join dbo.Ac_BRM_User b on b.realname=a.[八級簽核] left join dbo.PCO_CheckMan c on c.UserName=b.Username
以上語句執行結果中，在簽核人，UserName，CheckManID不同時為空的情況下，有以下兩種情況：
如果UserName欄位為空，說明對應的簽核人在系統中不存在帳號，則暫停設置流程，請相關簽核人申請帳號后再做處理
如果UserName不為空，而CheckManID欄位為空，則說明對應的簽核人在系統中存在帳號，但是在表PCO_CheckMan中不存在帳號，則需要將帳號放入PCO_CheckMan中
--步驟3---建立游標----------
declare Step_cur scroll cursor for
select [流程號],[級數] from [zlq_PCO1107]
open Step_cur
declare @StartID int,@Step int
fetch first from Step_cur into @StartID,@Step
while @@fetch_status=0
begin
print convert(varchar(30),@StartID)+','+convert(varchar(30),@Step)
exec dbo.ljj_SetWorkFlow @StartID,@Step
fetch next from Step_cur into @StartID,@Step
end
close Step_cur
deallocate Step_cur
------------------------------------------
--delete dbo.PCO_WorkFlowStep where stepid in(select distinct StepID from dbo.PCO_WorkFlowStep where StepID>=219320 and StepID<=229543)
select a.StepID,b.[流程號]
from dbo.PCO_WorkFlowStep a
left join dbo.[zlq_PCO1107] b on a.StepID=b.[流程號]
where StepID>=229540 and StepID<=229549
exec dbo.ljj_SetWorkFlow 229540,4
以上這些語句是在導入數據且執行建立游標后，在設置簽核條件這一步驟時出現錯誤的補救方法
--步驟4---設置簽核條件----------
select * from dbo.zlq_PCO1107 where uprange='+∞'
update [zlq_PCO1107]
set [UpRange]=8888 where uprange='+∞'
select * from dbo.zlq_PCO1107 where downrange='-∞'
update [zlq_PCO1107]
set [DownRange]=-8888 where downrange='-∞'
INSERT INTO [QBRClient].[dbo].[PCO_CheckCondition]
([ProjectCode], [CommodityCode], [UpRange], [DownRange], [SupplierFlag], [StepID], [Remark])
SELECT [ProjectCode], [Commoditycode],convert(decimal(38,6),[UpRange]),convert(decimal(38,6),[DownRange]), [內外交或其它],[流程號], [級數]
FROM [QBRClient].[dbo].[zlq_PCO1107]
select * from dbo.PCO_Checkcondition where UpRange=8888
update dbo.PCO_Checkcondition
set UpRange=99999999999999999999999999999999
where UpRange=8888
select * from dbo.PCO_Checkcondition where DownRange=-8888
update dbo.PCO_Checkcondition
set DownRange=-99999999999999999999999999999999
where DownRange=-8888
--步驟5---設置簽核帳號----------
INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID] , [CheckManID])
select [流程號],(select CheckmanID from dbo.PCO_CheckMan
where UserName=(select UserName from dbo.member where Isvalid=1 and Realname=[一級簽核]))
from dbo.[zlq_PCO1107] where [一級簽核] is not null
INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID] , [CheckManID])
select [流程號]+1,(select CheckmanID from dbo.PCO_CheckMan
where UserName=(select UserName from dbo.member where Isvalid=1 and Realname=[二級簽核]))
from dbo.[zlq_PCO1107] where [二級簽核] is not null
INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID] , [CheckManID])
select [流程號]+2,(select CheckmanID from dbo.PCO_CheckMan
where UserName=(select UserName from dbo.member where Isvalid=1 and Realname=[三級簽核]))
from dbo.[zlq_PCO1107] where [三級簽核] is not null
INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID] , [CheckManID])
select [流程號]+3,(select CheckmanID from dbo.PCO_CheckMan
where UserName=(select UserName from dbo.member where Isvalid=1 and Realname=[四級簽核]))
from dbo.[zlq_PCO1107] where [四級簽核] is not null
INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID] , [CheckManID])
select [流程號]+4,(select CheckmanID from dbo.PCO_CheckMan
where UserName=(select UserName from dbo.member where Isvalid=1 and Realname=[五級簽核]))
from dbo.[zlq_PCO1107] where [五級簽核] is not null
INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID] , [CheckManID])
select [流程號]+5,(select CheckmanID from dbo.PCO_CheckMan
where UserName=(select UserName from dbo.member where Isvalid=1 and Realname=[六級簽核]))
from dbo.[zlq_PCO1107] where [六級簽核] is not null
INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID] , [CheckManID])
select [流程號]+6,(select CheckmanID from dbo.PCO_CheckMan
where UserName=(select UserName from dbo.member where Isvalid=1 and Realname=[七級簽核]))
from dbo.[zlq_PCO1107] where [七級簽核] is not null
INSERT INTO [QBRClient].[dbo].[PCO_WorkFlowChecker]([StepID] , [CheckManID])
select [流程號]+7,(select CheckmanID from dbo.PCO_CheckMan
where UserName=(select UserName from dbo.member where Isvalid=1 and Realname=[八級簽核]))
from dbo.[zlq_PCO1107] where [八級簽核] is not null
--步驟6----檢查同一種條件下是否存在一人簽多次的流程-------------
select * from dbo.PCO_WorkFlowChecker
where id in(
select a.id from dbo.PCO_WorkFlowChecker a (nolock)
inner join dbo.PCO_WorkFlowChecker b (nolock) on a.checkmanid=b.checkmanid
and a.stepid+1=b.stepid
)
---步驟7--------------檢查PCO流程設置是否斷點-----------------------
根據PCO簽核條件表中STEPID不為0的記錄數﹐去跟簽核流程表(PCO_WorkFlowStep)
中的最后一級簽核步驟數(nextID為0的步驟)比較﹐如果相同﹐則說明無斷點﹐可
簽核流程可走通。
select count(*) from dbo.PCO_WorkFlowStep
where stepid in
(
select a.stepid+c.Remark-1 from dbo.PCO_WorkFlowChecker a (nolock)
--inner join dbo.PCO_WorkFlowStep b (nolock) on a.stepid=b.stepid
inner join dbo.PCO_CheckCondition c (nolock) on a.stepid=c.stepid
)
and nextid=0
---------步驟8-------簽核人在簽核流程里是否正確---------
select * from dbo.PCO_WorkFlowStep (nolock)
where StepID>=229550 and StepID<=229550
*/
CREATE proc dbo.ljj_SetWorkFlow
@SetpID int,
@Level int
as
declare @err varchar(200)
declare @i int
if exists(select * from dbo.PCO_WorkFlowStep where StepID>=@SetpID and StepID<=(@SetpID+@Level))
begin
select @err='您設置的流程SetpID='+convert(varchar(30),@SetpID)+',簽核層級='+convert(varchar(30),@Level)+' 在系統中已經存在,無法繼續設置簽核流程!'
----print @err
raiserror(@err,16,1)
return 111
end
--第一級---------------------------
--print '--第一級---------------------------'
--print @SetpID
if @Level=1
begin
insert into dbo.PCO_WorkFlowStep
( StepID,PreID,NextID )
values(@SetpID, 0 ,0 )
end
if @Level>1
begin
insert into dbo.PCO_WorkFlowStep
( StepID,PreID,NextID )
values(@SetpID, 0 ,@SetpID+1)
end
--第二級到倒數第二級---------------
--print '--第二級到倒數第二級----------------------'
select @i=(@SetpID+1)
while @i<(@SetpID+@Level-1)
begin
--print @i
insert into dbo.PCO_WorkFlowStep
(StepID,PreID,NextID)
values( @i ,@i-1 ,@i+1 )
select @i=@i+1
end
--最後一級-------------------------
--print '--最後一級---------------------------'
--print @SetpID+@Level-1
if @Level>1
begin
insert into dbo.PCO_WorkFlowStep
( StepID , PreID ,NextID)
values(@SetpID+@Level-1,@SetpID+@Level-2,0 )
end
--狀態多選
--如果有狀態為A,B,C,D,E,F等區域,可多選.
--則可用編碼A:1,B:2,C:4,D:8,E:16,F:32,G:64,m選擇A,D則為1+8=9,n選則B,C則為2+4=6
--檢查m及n之所有選擇
select 9|7
--檢查m及n之交集
select 9&7
--如果判斷其所在區域可用
DECLARE @I int
set @I=9&7
select 'A' where @I&1=1 --查是否為A
select 'B' where @I&2=2 --查是否為B
select 'C' where @I&4=4 --查是否為C
select 'D' where @I&8=8 --查是否為D
select 'E' where @I&16=16 --查是否為E
select 'F' where @I&32=32 --查是否為F
select 'G' where @I&64=64 --查是否為G
--實例
select * from (
select 'NO' no,26 vaule
) a join (
select 1 code,'A' name
union all select 2 code,'B' name
union all select 4 code,'C' name
union all select 8 code,'D' name
union all select 16 code,'E' name
union all select 32 code,'F' name
) b
on a.vaule&b.code=b.code
-----------------------橫表轉豎表的範例1------------------------------------------
---------臨時表列名查找-----------------------------
SELECT OBJECT_ID('tempdb.qbrclient.#tt')
select * from tempdb.dbo.syscolumns
where ID=OBJECT_ID('tempdb.qbrclient.#tt')
----------------------------------------------
select * from dbo.Forecast_MaterialGroup_flat
where MaterialGroupID in ('4D6404A2-468C-483C-9856-0000A1F63651','8F85B6C3-B2FA-4B37-9C3D-0001062461D5','A8F163E8-F956-46A5-903C-0002F80B6AF4')
declare @Fields table(HorField varchar(50))
insert into @Fields(Horfield)
select distinct name
from syscolumns
where ID=object_ID('dbo.Forecast_MaterialGroup_flat')
and name<>'MaterialGroupID'
and name>='2006-01'
and name<='2006-12'
order by name
--select * from @Fields
declare @tname varchar(50)
select @tname='##'+replace(newID(),'-','_')
exec('create Table '+@tname+'(MaterialGroupID uniqueidentifier,ForecastMonth varchar(7),ForecastQty bigint)')
declare HorToVer cursor scroll for
select HorField from @Fields
open HorToVer
declare @Field varchar(50)
fetch first from HorToVer into @Field
while @@Fetch_Status=0
begin
declare @insert varchar(2000)
select @insert='insert into '+@tname+'(MaterialGroupID,ForecastMonth,ForecastQty)
select MaterialGroupID,'''+@Field+''' as ForecastMonth,['+@Field+'] from dbo.Forecast_MaterialGroup_flat
where MaterialGroupID in (''4D6404A2-468C-483C-9856-0000A1F63651'',''8F85B6C3-B2FA-4B37-9C3D-0001062461D5'',''A8F163E8-F956-46A5-903C-0002F80B6AF4'')'
print @insert
exec(@insert)
fetch next from HorToVer into @Field
end
close HorToVer
deallocate HorToVer
exec('select * from '+@tname+' order by MaterialGroupID,ForecastMonth')
exec('drop table '+@Tname)
--select * from ##7598C1EC_65FE_4B23_AD5C_D2205F50840B
---------------------------橫表轉豎表的範例1------------------------------------------
drop table #mg
create table #mg(MaterialGroupID uniqueidentifier,ForecastMonth varchar(7),ForecastQty int)
declare htov cursor scroll
for
select name from dbo.syscolumns
where ID=object_ID('dbo.Forecast_MaterialGroup_Flat')
and name like('2006_%')
open htov
declare @col varchar(50)
fetch first from htov into @col
while @@fetch_status=0
begin
declare @sql varchar(200)
select @sql='insert into #mg
select MaterialGroupID,'''+@col+''',['+@col+']
from dbo.Forecast_MaterialGroup_Flat
where ['+@col+']>0'
print @sql
exec(@sql)
fetch next from htov into @col
end
close htov
deallocate htov
select * from #mg
------竪表轉橫表---------------------
----------------------分組統計-----------------------------
create table dbo.test(name varchar(50),nj varchar(50),km varchar(50),cj int)
insert into dbo.test(name,nj,km,cj) values('李四','一年級','英語',98)
insert into dbo.test(name,nj,km,cj) values('張三','一年級','語文',90)
insert into dbo.test(name,nj,km,cj) values('張三','一年級','數學',99)
insert into dbo.test(name,nj,km,cj) values('張三','一年級','英語',78)
insert into dbo.test(name,nj,km,cj) values('王五','一年級','語文',80)
insert into dbo.test(name,nj,km,cj) values('王五','一年級','數學',89)
insert into dbo.test(name,nj,km,cj) values('王五','一年級','英語',88)
insert into dbo.test(name,nj,km,cj) values('馬六','一年級','語文',70)
insert into dbo.test(name,nj,km,cj) values('馬六','一年級','數學',79)
insert into dbo.test(name,nj,km,cj) values('馬六','一年級','英語',88)
insert into dbo.test(name,nj,km,cj) values('王五','二年級','英語',89)
insert into dbo.test(name,nj,km,cj) values('王五','二年級','語文',81)
insert into dbo.test(name,nj,km,cj) values('王五','二年級','數學',90)
insert into dbo.test(name,nj,km,cj) values('李四','二年級','英語',99)
insert into dbo.test(name,nj,km,cj) values('馬六','二年級','英語',89)
insert into dbo.test(name,nj,km,cj) values('馬六','二年級','語文',71)
insert into dbo.test(name,nj,km,cj) values('馬六','二年級','數學',80)
insert into dbo.test(name,nj,km,cj) values('張三','二年級','英語',79)
insert into dbo.test(name,nj,km,cj) values('張三','二年級','語文',91)
insert into dbo.test(name,nj,km,cj) values('張三','二年級','數學',100)
declare @s varchar(8000)
select @s='select * from
(
select
CASE WHEN (GROUPING(nj) = 1) and (grouping(name)<>1) THEN ''SUMnj''
WHEN (GROUPING(nj) = 1) and (grouping(name) =1) THEN ''SUMALL''
ELSE ISNULL(nj, ''UNKNOWN'') END AS nj,
CASE WHEN (grouping(name)=1) and (GROUPING(nj) <> 1) THEN ''SUMkm''
WHEN (grouping(name)=1) and (GROUPING(nj) = 1) THEN ''SUMALL''
ELSE ISNULL(name, ''UNKNOWN'') END AS name'
select @s=@s+','+char(10)+'sum(case when nj='''+nj+''' and km='''+km+''' then cj else 0 end) as ['+nj+'_'+km+']'
from (select distinct nj,km from dbo.test) a
order by nj,km
select @s=@s+','+char(10)+'sum(case when nj='''+nj+''' then cj else 0 end) as ['+nj+']'
from (select distinct nj from dbo.test) a
order by nj
-- select @s=@s+','+char(10)+'sum(case when km='''+km+''' then cj else 0 end) as ['+km+']'
-- from (select distinct km from dbo.test) a
-- order by km
select @s=@s+char(10)+'from dbo.test group by nj,name with cube '
select @s=@s+char(10)+') aa'
select @s=@s+char(10)+'where nj<>''SUMnj'''
select @s=@s+char(10)+'order by name desc,nj'+char(10)
print @s
exec(@s)
--Data Type數據類型
/*
new type:
1.bigint--8B--int
2.sql_variant--主要解決數據兼容問題而提出的數據類型，可存儲不同數據類型的數據。
3.table--主要應用在編程環境中的數據類型，可以用臨時存儲從表格中取出的數據信息。
int type
1.bit= {0 | 1}--2B
2.bigint {-2^63--2^63-1}--8B--max
3.integer {-2^31--2^31-1}--4B
4.smallint{-2^15--2^15-1}--2B
5.tinyint {0--255}--1B
money type--只能維持萬分之一的精度，有再高要求就要用到數字數據類型
1.money {-2^63--2^63-1} 數據類型存儲的貨幣值兩個4B整數構成，前一個表示貨幣值的整數部分，后一個表示貨幣值得小數部分。
2.smallmoney{-2^31--2^31-1} 數據類型存儲的貨幣值兩個4B整數構成，前一個表示貨幣值的整數部分，后一個表示貨幣值得小數部分。
數字數據類型--精度取值範圍是1-128，但如果使用高精度命令方式啓動SQL Server（配置啓動參數/p）,則可高達３８位。
1.decimal{-10^38-1--10^38-1}
2.numeric{-10^38-1--10^38-1}
浮點數據類型
1.float{-1.79+308--1.79+308}
2.real {-3.40+308--3.40+308}
date type
1.datetime {1753.1.1--9999.12.31}--8B--可以精確到3/100s,尾數被圓整為000,003,007ms
2.smalldatetime{1900.1.1--2079.6.6 }--4B--可以精確到m
char type
1.char(8000)
2.varchar(8000)
3.text(2G{2^31-1}--容量受@@textsize的限制
二進制數據類型
1.binary --<8KB
2.varbinary--<8KB
3.image -->8KB
統一碼數據類型
1.nchar (1--4000)
2.nvarchar(0--4000)
3.ntext (0--2^30-1)
table type
declare @tablevar table
(Col1 int primary key,Col2 char(3))
insert @tablevar values(1,'aaa')
insert @tablevar values(2,'bbb')
select * from @tablevar
--SQL 查詢屬性
/*
set nocount {on | off}--set on then no return rowcount
set nocount on
select * from dbo.testljj
print @@rowcount
set nocount off
select * from dbo.testljj
print @@rowcount
set noexec {on | off}--on 編譯不執行 off 編譯后執行
set noexec on
select * from dbo.testljj
set noexec off
select * from dbo.testljj
set parseonly {on | off}--parse(分析)on 只檢查語法不編譯執行 off 檢查語法編譯執行
set parseonly on
select * from dbo.testljj
set parseonly off
select * from dbo.testljj
set rowcount {int}--返回給定的行就會自動停止執行
set rowcount 1
select * from dbo.testljj
select @@rowcount
set rowcount 10000000
select * from dbo.testljj
select @@rowcount
set lock_timeout {int}--以ms為單位設置當前查詢等待被服務器封鎖的數據被釋放所需要的時間
set lock_timeout 1
select @@lock_timeout
select * from dbo.testljj
set lock_timeout 2
select * from dbo.testljj
select @@lock_timeout
set showplan_all {on | off}--on Server不執行這個查詢而是返回所有的查詢語句，
--以及這些語句將如何被執行，執行時消耗的系統資源等信息，off為正常
--該語句只能出現在單獨的批處理中，不能將他用於存儲過程。
set showplan_all on
select * from dbo.testljj
set showplan_all off
select * from dbo.testljj
set showplan_text {on | off}--on Server不執行這個查詢而是返回所有的查詢語句，
--以及這些語句將如何被執行的文本信息，off為正常
--該語句只能出現在單獨的批處理中，不能將他用於存儲過程。
set showplan_text on
select * from dbo.testljj
set showplan_text off
select * from dbo.testljj
*/
--**************去除特殊的字符方法用replace()函數*****************************************
declare @st varchar(100)
select @st=',asdf,,,,,,,,,asdf, xx'+char(32)+'yy'+char(13)+'zz'+char(10)+'pp'+char(9)+'tt,'
print @st
select @st=replace(replace(replace(replace(@st,char(32),''),char(9),''),char(10),''),char(13),'')
while @st<>replace(@st,',,',',')
begin
select @st=replace(@st,',,',',')
print '--'+@st
end
if left(@st,1)=','
begin
select @st=right(@st,len(@st)-1)
print '--'+@st
end
if right(@st,1)=','
begin
select @st=left(@st,len(@st)-1)
print '--'+@st
end
------------統計User使用次數-----------------------------------------------------------
select UpdateBy,count,day,convert(decimal(11,2),(count*1.0/(case when day=0 then 1 else day end))) as UseTime,beginDT,EndDT
from
(
select e.UpdateBy,count(*) as count,
datediff(dd,(select min(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy),
(select max(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy)
) as Day,
-- count(*)/datediff(dd,(select min(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy),
-- (select max(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy)
-- ) as Usetime,
(select min(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy) as beginDT,
(select max(UpdateDT) from dbo.ExeVersion where UpdateBy=e.UpdateBy) as EndDT
from dbo.ExeVersion e
inner join dbo.member m on e.updateby=m.Username
where m.isvalid=1
group by e.UpdateBy
--order by count(*) desc
) xx
order by count desc,UseTime desc
-----------------------------------------------------------------------