SQLÖÐEXISTSµÄÓ÷¨
±ÈÈçÔÚNorthwindÊý¾Ý¿âÖÐ
ÓÐÒ»¸ö²éѯΪ
SELECT c.CustomerId, CompanyName
from Customers c
WHERE EXISTS(
SELECT OrderID from Orders o
WHERE o.CustomerID = cu.CustomerID)
ÕâÀïÃæµÄEXISTSÊÇÈçºÎÔË×÷ÄØ£¿×Ó²éѯ·µ»ØµÄÊÇOrderId×ֶΣ¬¿ÉÊÇÍâÃæµÄ²éѯҪÕÒµÄÊÇCustomerIDºÍCompanyName×ֶΣ¬ÕâÁ½¸ö×ֶο϶¨²»ÔÚOrderIDÀïÃæ°¡£¬ÕâÊÇÈçºÎÆ¥ÅäµÄÄØ£¿
EXISTSÓÃÓÚ¼ì²é×Ó²éѯÊÇ·ñÖÁÉٻ᷵»ØÒ»ÐÐÊý¾Ý£¬¸Ã×Ó²éѯʵ¼ÊÉϲ¢²»·µ»ØÈκÎÊý¾Ý£¬¶øÊÇ·µ»ØÖµTrue»òFalse
EXISTS
Ö¸¶¨Ò»¸ö×Ó²éѯ£¬¼ì²âÐеĴæÔÚ¡£
Óï·¨
EXISTS subquery
²ÎÊý
subquery
ÊÇÒ»¸öÊÜÏÞµÄ SELECT Óï¾ä (²»ÔÊÐíÓÐ COMPUTE ×Ó¾äºÍ INTO ¹Ø¼ü×Ö)¡£Óйظü¶àÐÅÏ¢£¬Çë²Î¼û SELECT ÖÐÓйØ×Ó²éѯµÄÌÖÂÛ¡£
½á¹ûÀàÐÍ
Boolean
½á¹ûÖµ
Èç¹û×Ó²éѯ°üº¬ÐУ¬Ôò·µ»Ø TRUE¡£
ʾÀý
A. ÔÚ×Ó²éѯÖÐʹÓà NULL ÈÔÈ»·µ»Ø½á¹û¼¯
Õâ¸öÀý×ÓÔÚ×Ó²éѯÖÐÖ¸¶¨ NULL£¬²¢·µ»Ø½á¹û¼¯£¬Í¨¹ýʹÓà EXISTS ÈÔȡֵΪ TRUE¡£
USE Northwind
GO
SELECT CategoryName
from Categories
WHERE EXISTS (SELECT NULL)
ORDER BY CategoryName ASC
GO
B. ±È½ÏʹÓà EXISTS ºÍ IN µÄ²éѯ
Õâ¸öÀý×ӱȽÏÁËÁ½¸öÓïÒåÀàËƵIJéѯ¡£µÚÒ»¸ö²éѯʹÓà EXISTS ¶øµÚ¶þ¸ö²éѯʹÓà IN¡£×¢ÒâÁ½¸ö²éѯ·µ»ØÏàͬµÄÐÅÏ¢¡£
USE pubs
GO
SELECT DISTINCT pub_name
from publishers
WHERE EXISTS
(SELECT *
from titles
WHERE pub_id = publishers.pub_id
AND type = \'business\')
GO
-- Or, using the IN clause:
USE pubs
GO
SELECT distinct pub_name
from publishers
WHERE pub_id IN
(SELECT pub_id
from titles
WHERE type = \'business\')
GO
ÏÂÃæÊÇÈÎÒ»²éѯµÄ½á¹û¼¯£º
pub_name &nbs
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃ棬ºóÃæ ......
ËøµÄ¸ÅÊö ¡¡¡¡Ò». ΪʲôҪÒýÈëËø
¡¡¡¡¶à¸öÓû§Í¬Ê±¶ÔÊý¾Ý¿âµÄ²¢·¢²Ù×÷ʱ»á´øÀ´ÒÔÏÂÊý¾Ý²»Ò»ÖµÄÎÊÌ⣺
¡¡¡¡¶ªÊ§¸üÐÂ
¡¡¡¡A£¬BÁ½¸öÓû§¶ÁͬһÊý¾Ý²¢½øÐÐÐ޸ģ¬ÆäÖÐÒ»¸öÓû§µÄÐ޸Ľá¹ûÆÆ»µÁËÁíÒ»¸öÐ޸ĵĽá¹û£¬±ÈÈ綩Ʊϵͳ
¡¡¡¡Ôà¶Á
¡¡¡¡AÓû§ÐÞ¸ÄÁËÊý¾Ý£¬ËæºóBÓû§ÓÖ¶Á³ö¸ÃÊý¾Ý£¬µ«AÓû§ÒòΪijЩÔÒòÈ¡ÏûÁ˶ÔÊý¾ ......
½ñÌ칤×÷時ºò輸Èë數據庫ÖÐÓÐÒ»條數據×Ö¶Î記錄ÊÇ"¿Í戶·ñ¾ö"
ʹÓÃ
select * from TelephoneStatusCategory where CategoryName like '%¿Í戶·ñ%'
select * from TelephoneStatusCategory where CategoryName like '%¿Í戶·ñ¾ö% ......
ÀýÈç:
select getdate()
2004-09-12 11:06:08.177
Õâ¶ÔÓÚÔÚÒª²»Í¬Êý¾Ý¿â¼äתÒÆÊý¾Ý»òÕßÏ°¹ßoracleÈÕÆÚ¸ñʽYYYY-MM-DD HH24:MI:SSµÄÈ˶àÉÙÓÐЩ²»·½±ã.
ÎÒÕûÀíÁËÒ»ÏÂSQL ServerÀïÃæ¿ÉÄܾ³£»áÓõ½µÄÈÕÆÚ¸ñʽת»»·½·¨:
¾ÙÀýÈçÏÂ:
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replac ......
Pl/sql ±à³ÌÖ®case whenµÄÓ÷¨£º£¨t3.LOCAL_BF_KND='111000' -- ±¾Ô»ù±¾ÔÂ×â·Ñ£©
select sum(t1.FAV_FEE) from bi_rw.FO_A_2FAV_M_G t1, bi_rw.D_BILLFEE_KND t3 where t1.BILLFEE_KND=t3.BILLFEE_KND(+) and t3.LOCAL_BF_KND='111000' --1310557.76
Èç¹û²»±ãÓÚ°ÑÌõ¼þt3.LOCAL_BF_KND='111000' дÔÚwhereÌ ......