SQLÖеÄinºÍexistsµÄÇø±ð
ÔÚÍøÉÏ¿´µ½Ò»¸öºÜºÃµÄÀý×Ó½²½âinºÍexistsµÄÇø±ð£¬ÕâÀﱸÍüÏ¡£
±¾Ê¾ÀýËùʾ²éѯ²éÕÒÓÉλÓÚÒÔ×Öĸ B ¿ªÍ·µÄ³ÇÊÐÖеÄÈÎÒ»³ö°æÉ̳ö°æµÄÊéÃû£º
USE pubs
SELECT title
from titles WHERE EXISTS
(SELECT *
from publishers
WHERE pub_id = titles.pub_id
AND city LIKE 'B%')
GO
-- Or, using IN:
USE pubs
GO
SELECT title
from titles
WHERE pub_id IN
(SELECT pub_id
from publishers
WHERE city LIKE 'B%')
GO
ʹÓà NOT EXISTS
NOT EXISTS µÄ×÷ÓÃÓë EXISTS ÕýÏà·´¡£Èç¹û×Ó²éѯûÓзµ»ØÐУ¬ÔòÂú×ã NOT EXISTS ÖÐµÄ WHERE ×Ӿ䡣±¾Ê¾Àý²éÕÒ²»³ö°æÉÌÒµÊé¼®µÄ³ö°æÉ̵ÄÃû³Æ£º
USE pubs
GO
SELECT pub_name
from publishers
WHERE NOT EXISTS
(SELECT *
from titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
ORDER BY pub_name
GO
inºÍexists
in ÊÇ°ÑÍâ±íºÍÄÚ±í×÷hash Á¬½Ó£¬¶øexistsÊǶÔÍâ±í×÷loopÑ»·£¬Ã¿´ÎloopÑ»·ÔÙ¶ÔÄÚ±í½øÐвéѯ¡£Ò»Ö±ÒÔÀ´ÈÏΪexists±ÈinЧÂʸߵÄ˵·¨ÊDz»×¼È·µÄ¡£
Èç¹û²éѯµÄÁ½¸ö±í´óСÏ൱£¬ÄÇôÓÃinºÍexists²î±ð²»´ó¡£
Èç¹ûÁ½¸ö±íÖÐÒ»¸ö½ÏС£¬Ò»¸öÊÇ´ó±í£¬Ôò×Ó²éѯ±í´óµÄÓÃexists£¬×Ó²éѯ±íСµÄÓÃin£º
ÀýÈ磺±íA£¨Ð¡±í£©£¬±íB£¨´ó±í£©£ºselect * from A where cc in (select cc from B)
ЧÂʵͣ¬Óõ½ÁËA±íÉÏccÁеÄË÷Òý£»select * from A where exists(select cc from B where cc=A.cc)
ЧÂʸߣ¬Óõ½ÁËB±íÉÏccÁеÄË÷Òý¡£
Ïà·´µÄ2£ºselect * from B where cc in (select cc from A)
ЧÂʸߣ¬Óõ½ÁËB±íÉÏccÁеÄË÷Òý£»select * from B where exists(select cc from A where cc=B.cc)
ЧÂʵͣ¬Óõ½ÁËA±íÉÏccÁеÄË÷Òý¡£
not in ºÍnot existsÈç¹û²éѯÓï¾äʹÓÃÁËnot in ÄÇôÄÚÍâ±í¶¼½øÐÐÈ«±íɨÃ裬ûÓÐÓõ½Ë÷Òý£»¶ønot extsts µÄ×Ó²éѯÒÀÈ»ÄÜÓõ½±íÉϵÄË÷Òý¡£ËùÒÔÎÞÂÛÄǸö±í´ó£¬ÓÃnot exists¶¼±Ènot inÒª¿ì¡£
in Óë =µÄÇø±ð
select name from student where name in ('zhang','wang','li','zhao');
Óë
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
Ïà¹ØÎĵµ£º
/*----------------------------------------------------------------
-- Author :feixianxxx(poofly)
-- Date :2010-04-20 20:10:41
-- Version:
-- Microsoft SQL Server 2008 (SP1) - ......
Ò»¸ö¼òµ¥µÄÀý×Ó£º
ÏȽ¨Ò»¸öC#Àࣺ
ÒýÓÃSystem.Data.Linq.dll³ÌÐò¼¯£¬
using System.Data.Linq.MappingºÍ
using System.Data.Linq Á½¸ö¿Õ¼ä¡£
[Table]
public class Inventory
{
[Column]
public string Make;
[Column]
public string Color;
&nbs ......
CONVERT
½«Ä³ÖÖÊý¾ÝÀàÐ͵ıí´ïʽÏÔʽת»»ÎªÁíÒ»ÖÖÊý¾ÝÀàÐÍ¡£ÓÉÓÚijЩÐèÇó¾³£Óõ½È¡ÈÕÆÚ¸ñʽµÄ²»Í¬.ÏÖÒÔÏ¿ÉÔÚ
SQL ServerÖÐ ½«ÈÕÆÚ¸ñʽ»¯.
SQL Server Ö§³ÖʹÓÿÆÍþÌØËã·¨µÄ°¢À²®ÑùʽÖеÄÊý¾Ý¸ñʽ¡£
ÔÚ±íÖУ¬×ó²àµÄÁ½Áбíʾ½« datetime »ò smalldatetime ת»»Îª×Ö·ûÊý¾ÝµÄ style Öµ¡£¸ø style Öµ¼Ó 100£¬¿É»ñµÃ°ü ......
sql ·þÎñÆ÷½ÇÉ« Êý¾Ý¿â½ÇÉ«
·þÎñÆ÷½ÇÉ«
ËùÓеķþÎñÆ÷½ÇÉ«¶¼ÊÇ“¹Ì¶¨µÄ”½ÇÉ«£¬²¢ÇÒ£¬´ÓÒ»¿ªÊ¼¾Í´æÔÚÓÚÄÇÀï——×Ô°²×°ÍêSQL ServerµÄÄÇÒ»¿ÌÆð£¬Ä㽫ӵÓеÄËùÓзþÎñÆ÷½ÇÉ«¾ÍÒѾ´æÔÚÁË¡£
½Ç¡¡¡¡É«
ÌØ¡¡¡¡ÐÔ
sysadmin
¸Ã½ÇÉ«Äܹ»Ö´ÐÐSQL ServerÉϵÄÈκβÙ× ......