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
----------------------------------------
Algodata Infosystems
New Moon Books &n
Ïà¹ØÎĵµ£º
ÔÚLINQ to EntitiesÖÐûÓа취ÔÙÏñ LINQ to SQL ÖÐÒ»ÑùʹÓà Contains µÄ·½·¨À´ÊµÏÖsql "in" ¹Ø¼ü×Ö
ÏÂÃæ´úÂëÔÚ LINQ to SQL ÖпÉÐÐ ÔÚLINQ to EntitiesÈ´ÎÞ·¨ÔËÐУº
var s = db.Account.Select(c => c.ID);
var ret =(from t in db.Profile
where&n ......
Ëæ×ÅB/SģʽӦÓÿª·¢µÄ·¢Õ¹£¬Ê¹ÓÃÕâÖÖģʽ±àдӦÓóÌÐòµÄ³ÌÐòÔ±Ò²Ô½À´Ô½¶à¡£µ«ÊÇÓÉÓÚ³ÌÐòÔ±µÄˮƽ¼°¾ÑéÒ²²Î²î²»Æ룬Ï൱´óÒ»²¿·Ö³ÌÐòÔ±ÔÚ±àд´úÂëµÄʱºò£¬Ã»ÓжÔÓû§ÊäÈëÊý¾ÝµÄºÏ·¨ÐÔ½øÐÐÅжϣ¬Ê¹Ó¦ÓóÌÐò´æÔÚ°²È«Òþ»¼¡£Óû§¿ÉÒÔÌá½»Ò»¶ÎÊý¾Ý¿â²éѯ´úÂ룬¸ù
¾Ý³ÌÐò·µ»ØµÄ½á¹û£¬»ñµÃijЩËûÏëµÃÖªµÄÊý¾Ý£¬Õâ¾ÍÊÇËùνµÄSQ ......
ÈÕÖ¾ÎļþÂú¶øÔì³ÉSQLÊý¾Ý¿âÎÞ·¨Ð´ÈëÎļþʱ£¬¿ÉÓÃÁ½ÖÖ·½·¨£º
¡¡¡¡Ò»ÖÖ·½·¨£ºÇå¿ÕÈÕÖ¾¡£
¡¡¡¡1£®´ò¿ª²éѯ·ÖÎöÆ÷£¬ÊäÈëÃüÁî
¡¡¡¡DUMP TRANSACTION Êý¾Ý¿âÃû WITH NO_LOG
¡¡¡¡2.ÔÙ´ò¿ªÆóÒµ¹ÜÀíÆ÷--ÓÒ¼üÄãҪѹËõµÄÊý¾Ý¿â--ËùÓÐÈÎÎñ--ÊÕËõÊý¾Ý¿â--ÊÕËõÎļþ--Ñ¡ÔñÈÕÖ¾Îļþ--ÔÚÊÕËõ·½Ê½ÀïÑ¡ÔñÊÕËõÖÁXXM,ÕâÀï»á¸ø³öÒ»¸öÔÊÐíÊÕËõµ ......
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
select dateadd(day,2,'2004-10-15') --·µ»Ø£º2004-10-17 00:00:00.000
3. datediff ·µ»Ø¿çÁ½¸öÖ¸¶¨ÈÕÆÚµÄÈÕÆÚºÍʱ¼ä ......
PL/SQL DEVELOPER »ù±¾Ó÷¨Ïê½â(½¨Òéд¹ýµÚÒ»¸ö´æ´¢¹ý³ÌºóµÄ³õÊֱضÁ)
ÓùýoracleµÄ¶¼±§Ô¹£¬ÎªÁËÎȶ¨ ËüÌṩµÄͼÐλ¯²Ù×÷ ËÙ¶ÈÂýµÄÈÃÈËÉËÐÄѽ£¬p4+128MµÄ»úÆ÷Ö»ÒªÆô¶¯Ò»¸ö
oracle·þÎñ¾Í¹»ÈÃÈËÉËÐĵģ¬ÔÙÔÚdba studioÀïÃæ²Ù×÷ÕæÄÜÈÃÈË¿Þ¡£
pl/sql developerÕýÊǽâ¾öÕâ¸öÎÊÌâµ ......