SQL exists ºÍin
in ºÍ existsÆäʵͦºÃÇø±ðµÄ.ÇÒ¿´Ï±߻ù±¾¸ÅÄî¡£
in ÊÇÒ»¸ö¼¯ºÏÔËËã·û.
a in {a,c,d,s,d....}
Õâ¸öÔËËãÖÐ,Ç°ÃæÊÇÒ»¸öÔªËØ,ºóÃæÊÇÒ»¸ö¼¯ºÏ,¼¯ºÏÖеÄÔªËØÀàÐÍÊǺÍÇ°ÃæµÄÔªËØÒ»ÑùµÄ¡£ÊµÏֵĶԼ¯ºÏÖеÄÔªËؽøÐбéÀú£¬ÒÔÈ·¶¨¸ø¶¨µÄÔªËØaÊÇ·ñÓë×Ó²éѯ»òÁбíÖеÄÖµÏàÆ¥Åä¡£
in ÔËËãÓÃÔÚsqlÓï¾äÖÐ,ËüºóÃæ´øµÄselect Ò»¶¨ÊÇÑ¡Ò»¸ö×Ö¶Î,¶ø²»ÊÇselect *¡£
existsÊÇÒ»¸ö´æÔÚÅжÏ,Ö¸¶¨Ò»¸ö×Ó²éѯ£¬¼ì²âÐеĴæÔÚ¡£Èç¹ûºóÃæµÄ²éѯÖÐÓнá¹û,ÔòexistsΪÕæ,·ñÔòΪ¼Ù.
±ÈÈç˵ÄãÒªÅжÏij°àÊÇ·ñ´æÔÚÒ»¸öÃûΪ"СÃ÷"µÄѧÉú,Äã¿ÉÒÔÓÃin ÔËËã:
"СÃ÷" in (select sname from student)
ÕâÑù(select sname from student) ·µ»ØµÄÊÇÒ»¸öÈ«°àÐÕÃûµÄ¼¯ºÏ,inÓÃÓÚÅжÏ"СÃ÷"ÊÇ·ñΪ´Ë¼¯ºÏÖеÄÒ»¸öÊý¾Ý;
ͬʱ,ÄãÒ²¿ÉÒÔÓÃexistsÓï¾ä:
exists (select * from student where sname="СÃ÷")
ÕâÁ½¸öº¯ÊýÊDz¶àµÄ, µ«ÊÇÓÉÓÚÓÅ»¯·½°¸µÄ²»Í¬, ͨ³£NOT EXISTSÒª±ÈNOT IN Òª¿ì, ÒòΪNOT EXISTS¿ÉÒÔʹÓýáºÏËã·¨¶øNOT IN¡¡¾Í²»ÐÐÁË£¬¶øEXISTSÔò²»ÈçIN¿ì, ÒòΪÕâʱºòIN¿ÉÄܸü¶àµÄʹÓýáºÏËã·¨.
select * from ±íA where exists(select * from ±íB where ±íB.id=±íA.id)
Õâ¾äÏ൱ÓÚ
select * from ±íA where id in (select id from ±íB)
¶ÔÓÚ±íAµÄÿһÌõÊý¾Ý,¶¼Ö´ÐÐselect * from ±íB where ±íB.id=±íA.idµÄ´æÔÚÐÔÅжÏ,Èç¹û±íBÖдæÔÚ±íAµ±Ç°ÐÐÏàͬµÄid,ÔòexistsΪÕæ,¸ÃÐÐÏÔʾ,·ñÔò²»ÏÔʾ
exitsÊʺÏÄÚСÍâ´óµÄ²éѯ£¬inÊʺÏÄÚ´óÍâСµÄ²éѯ
exits Ï൱ÓÚ´æÔÚÁ¿´Ê£º±íʾ¼¯ºÏ´æÔÚ,Ò²¾ÍÊǼ¯ºÏ²»Îª¿ÕÖ»×÷ÓÃÒ»¸ö¼¯ºÏ.ÀýÈç exist P ±íʾP²»¿ÕʱΪÕæ; not exist P±íʾpΪ¿Õʱ ΪÕæ in±íʾһ¸ö±êÁ¿ºÍÒ»Ôª¹ØϵµÄ¹Øϵ¡£ÀýÈ磺s in P±íʾµ±sÓëPÖеÄij¸öÖµÏàµÈʱ ΪÕæ; s not in P ±íʾsÓëPÖеÄÿһ¸öÖµ¶¼²»ÏàµÈʱ ΪÕæ¡£
ÌرðÓ¦Óãº
ÓÉÓÚin ÔËËãÓÃÔÚsqlÓï¾äÖÐ,ËüºóÃæ´øµÄselect Ò»¶¨ÊÇÑ¡Ò»¸ö×Ö¶Î,¶ø²»ÊÇselect *¡£ËùÒÔÈç¹ûÔÚ²éѯÖÐÐèÒªÓõ½Áбí¶ÔÓ¦Ò»Ìõ¼Ç¼µÄ¶à¸ö×ֶεÄʱºò£¬Õâʱin¾Í²»ÊÊÓÃÁË£¬¶øÖ»ÄÜÓÃexists¡£
±ÈÈçÓÐÁ½¸ö±í¡£
ר¼Ò×é±í group
GroupId , MasterJury(Ö÷ÆÀ),SecondJury1(¸±ÆÀ1),SecondJury2£¨¸±ÆÀ2£©
ר¼Ò³ÉÔ±±í expert
UserId,UserName
ר¼ÒÆÀ·Ö±í scores
userid, scoreItem,score
ÕâʱÈçºÎÒª¶ÁÈ¡³öij¸öר¼Ò×éµÄ³ÉÔ±²¢ÏÔʾÈçÏÂÐÎʽ¾ÍÓеãÀ§ÄÑÁË
Ö÷ÆÀÈ
Ïà¹ØÎĵµ£º
´¥·¢Æ÷ʵ¼ÊÉϾÍÊÇÒ»ÖÖÌØÊâÀàÐ͵Ĵ洢¹ý³Ì£¬ÆäÌØÊâÐÔ±íÏÖÔÚ£ºËüÊÇÔÚÖ´ÐÐÌض¨µÄT-SQL ×Ô¶¯Ïà¼ÓµÄ¡£
´¥·¢Æ÷·ÖΪÁ½Àࣺ
&nbs ......
Èç¹û½á¹ûµÄ³¤¶È´óÓÚ·þÎñÆ÷²ÎÊýmax_allowed_packet£¬×Ö·û´®Öµº¯Êý·µ»ØNULL¡£¼û10.2.3 µ÷½Ú·þÎñÆ÷²ÎÊý¡£
¶ÔÓÚÕë¶Ô×Ö·û´®Î»ÖõIJÙ×÷£¬µÚÒ»¸öλÖñ»±ê¼ÇΪ1¡£
ASCII(str)
·µ»Ø×Ö·û´®strµÄ×î×óÃæ×Ö·ûµÄASCII´úÂëÖµ¡£Èç¹ûstrÊÇ¿Õ×Ö·û´®£¬·µ»Ø0¡£Èç¹ûstrÊÇNULL£¬·µ»ØNULL¡£
mysql> select ASCII('2');
-> ......
if object_id('tb') is not null drop table tb
go
create table tb([A] varchar(10),[B] int)
insert tb
select 'bcd',1 union all
select 'bcde',2 union all
select 'cd',3
go
¸öÈË·ÖÎöÕâµÀÌâµÄ˼·ÊÇ£º
ͨ¹ýSubstring() À´Ñ»··Ö¸î B ÁÐϵÄ×Ö·û´®¡£¡£
--ÏëµÃµ½µÄ½á¹û£º
/**
a b& ......
±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[ID] int IDENTITY (1,1)
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
Óï¾äÐÎʽ£º
SELECT TOP Ò³¼Ç¼ÊýÁ¿ *
from ±íÃû
WHERE (ID NOT IN
(SELECT TOP (ÿҳÐÐÊý*(Ò³Êý-1)) ID
from ±íÃû
ORDER BY ID))
ORDER BY ID
//×Ô¼º»¹¿ÉÒÔ¼ÓÉÏһЩ²éѯÌõ¼þ
Àý:
sele ......
create PROCEDURE sp_decrypt(@objectname varchar(50))
AS
begin
set nocount on
--CSDN£ºj9988 copyright:2004.07.15
--V3.2
--Æƽâ×Ö½Ú²»ÊÜÏÞÖÆ£¬ÊÊÓÃÓÚSQLSERVER2000´æ´¢¹ý³Ì£¬º¯Êý£¬ÊÓͼ£¬´¥·¢Æ÷
--ÐÞÕýÉÏÒ»°æ"ÊÓͼ´¥·¢Æ÷"²»ÄÜÕýÈ·½âÃÜ´íÎó
--·¢ÏÖÓÐ´í£¬ÇëE_MAIL£ºCSDNj9988@tom.com
begin tran
......