Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL ¶à±íÁªºÏ²éѯ

ºÜÉÙÓÃjoin£¬Õâ´Îѧѧ£¬²¢±¸ÍüÁ½ÆªÎÄÕ£¡
ת×Ô£ºhttp://hcx-2008.javaeye.com/blog/285661
Á¬½Ó²éѯ
ͨ¹ýÁ¬½ÓÔËËã·û¿ÉÒÔʵÏÖ¶à¸ö±í²éѯ¡£Á¬½ÓÊǹØÏµÊý¾Ý¿âÄ£Ð͵ÄÖ÷ÒªÌØµã£¬Ò²ÊÇËüÇø±ðÓÚÆäËüÀàÐÍÊý¾Ý¿â¹ÜÀíϵͳµÄÒ»¸ö±êÖ¾¡£
ÔÚ¹ØÏµÊý¾Ý¿â¹ÜÀíϵͳÖУ¬±í½¨Á¢Ê±¸÷Êý¾ÝÖ®¼äµÄ¹ØÏµ²»±ØÈ·¶¨£¬³£°ÑÒ»¸öʵÌåµÄËùÓÐÐÅÏ¢´æ·ÅÔÚÒ»¸ö±íÖС£µ±¼ìË÷Êý¾Ýʱ£¬Í¨¹ýÁ¬½Ó²Ù×÷²éѯ³ö´æ·ÅÔÚ¶à¸ö±íÖеIJ»Í¬ÊµÌåµÄÐÅÏ¢¡£Á¬½Ó²Ù×÷¸øÓû§´øÀ´ºÜ´óµÄÁé»îÐÔ£¬ËûÃÇ¿ÉÒÔÔÚÈκÎʱºòÔö¼ÓеÄÊý¾ÝÀàÐÍ¡£Îª²»Í¬ÊµÌå´´½¨ÐÂµÄ±í£¬¶ûºóͨ¹ýÁ¬½Ó½øÐвéѯ¡£
Á¬½Ó¿ÉÒÔÔÚSELECT Óï¾äµÄfrom×Ó¾ä»òWHERE×Ó¾äÖн¨Á¢£¬ËÆÊǶø·ÇÔÚfrom×Ó¾äÖÐÖ¸³öÁ¬½ÓʱÓÐÖúÓÚ½«Á¬½Ó²Ù×÷ÓëWHERE×Ó¾äÖеÄËÑË÷Ìõ¼þÇø·Ö¿ªÀ´¡£ËùÒÔ£¬ÔÚTransact-SQLÖÐÍÆ¼öʹÓÃÕâÖÖ·½·¨¡£
SQL-92±ê×¼Ëù¶¨ÒåµÄfrom×Ó¾äµÄÁ¬½ÓÓï·¨¸ñʽΪ£º
from join_table join_type join_table
[ON (join_condition)]
ÆäÖÐjoin_tableÖ¸³ö²ÎÓëÁ¬½Ó²Ù×÷µÄ±íÃû£¬Á¬½Ó¿ÉÒÔ¶Ôͬһ¸ö±í²Ù×÷£¬Ò²¿ÉÒÔ¶Ô¶à±í²Ù×÷£¬¶Ôͬһ¸ö±í²Ù×÷µÄÁ¬½ÓÓÖ³Æ×ö×ÔÁ¬½Ó¡£
join_type Ö¸³öÁ¬½ÓÀàÐÍ£¬¿É·ÖΪÈýÖÖ£ºÄÚÁ¬½Ó¡¢ÍâÁ¬½ÓºÍ½»²æÁ¬½Ó¡£ÄÚÁ¬½Ó(INNER JOIN)ʹÓñȽÏÔËËã·û½øÐбí¼äij(Щ)ÁÐÊý¾ÝµÄ±È½Ï²Ù×÷£¬²¢ÁгöÕâЩ±íÖÐÓëÁ¬½ÓÌõ¼þÏàÆ¥ÅäµÄÊý¾ÝÐС£¸ù¾ÝËùʹÓõıȽϷ½Ê½²»Í¬£¬ÄÚÁ¬½ÓÓÖ·ÖΪµÈÖµÁ¬½Ó¡¢×ÔÈ»Á¬½ÓºÍ²»µÈÁ¬½ÓÈýÖÖ¡£
ÍâÁ¬½Ó·ÖΪ×óÍâÁ¬½Ó(LEFT OUTER JOIN»òLEFT JOIN)¡¢ÓÒÍâÁ¬½Ó(RIGHT OUTER JOIN»òRIGHT JOIN)ºÍÈ«ÍâÁ¬½Ó(FULL OUTER JOIN»òFULL JOIN)ÈýÖÖ¡£ÓëÄÚÁ¬½Ó²»Í¬µÄÊÇ£¬ÍâÁ¬½Ó²»Ö»ÁгöÓëÁ¬½ÓÌõ¼þÏàÆ¥ÅäµÄÐУ¬¶øÊÇÁгö×ó±í(×óÍâÁ¬½Óʱ)¡¢ÓÒ±í(ÓÒÍâÁ¬½Óʱ)»òÁ½¸ö±í(È«ÍâÁ¬½Óʱ)ÖÐËùÓзûºÏËÑË÷Ìõ¼þµÄÊý¾ÝÐС£
½»²æÁ¬½Ó(CROSS JOIN)ûÓÐWHERE ×Ӿ䣬Ëü·µ»ØÁ¬½Ó±íÖÐËùÓÐÊý¾ÝÐеĵѿ¨¶û»ý£¬Æä½á¹û¼¯ºÏÖеÄÊý¾ÝÐÐÊýµÈÓÚµÚÒ»¸ö±íÖзûºÏ²éѯÌõ¼þµÄÊý¾ÝÐÐÊý³ËÒÔµÚ¶þ¸ö±íÖзûºÏ²éѯÌõ¼þµÄÊý¾ÝÐÐÊý¡£
Á¬½Ó²Ù×÷ÖеÄON (join_condition) ×Ó¾äÖ¸³öÁ¬½ÓÌõ¼þ£¬ËüÓɱ»Á¬½Ó±íÖеÄÁкͱȽÏÔËËã·û¡¢Âß¼­ÔËËã·ûµÈ¹¹³É¡£
ÎÞÂÛÄÄÖÖÁ¬½Ó¶¼²»ÄܶÔtext¡¢ntextºÍimageÊý¾ÝÀàÐÍÁнøÐÐÖ±½ÓÁ¬½Ó£¬µ«¿ÉÒÔ¶ÔÕâÈýÖÖÁнøÐмä½ÓÁ¬½Ó¡£ÀýÈ磺
SELECT p1.pub_id,p2.pub_id,p1.pr_info
from pub_info AS p1 INNER JOIN pub_info AS p2
ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)
(Ò»)ÄÚÁ¬½Ó
ÄÚÁ¬½Ó²éѯ²Ù×÷ÁгöÓëÁ¬½ÓÌõ¼þÆ¥ÅäµÄÊý¾ÝÐУ¬ËüʹÓñȽÏÔËËã·û±È½Ï±»Á¬½Ó


Ïà¹ØÎĵµ£º

sql ºº×ÖתÊ××ÖĸƴÒô

´ÓÍøÂçÉÏÊÕ¹ÎÁËһЩ£¬ÒÔ±¸ºóÓÃ
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--Èç¹û·Çºº×Ö×Ö·û£¬·µ»ØÔ­×Ö·û
set @PY=@PY+(case when unicode(@word) b ......

mysqlÊý¾Ý¿âsqlÎļþµ¼Èëµ¼³öÃüÁî

¡¡¡¡1.µ¼³öÕû¸öÊý¾Ý¿â
¡¡¡¡       mysqldump -u Óû§Ãû -p Êý¾Ý¿âÃû > µ¼³öµÄÎļþÃû
¡¡¡¡       mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
¡¡¡¡2.µ¼³öÒ»¸ö±í
¡¡¡¡      mysqldump -u Óû§Ãû -p Êý¾Ý¿âÃû ±íÃû> ......

SQLÁ¬½Ó²éѯ(»ã×Ü)

Ò»¡¢½»²æÁ¬½Ó£¨CROSS JOIN£©
½»²æÁ¬½Ó£¨CROSS JOIN£©£ºÓÐÁ½ÖÖ£¬ÏÔʽµÄºÍÒþʽµÄ£¬²»´øON×Ӿ䣬·µ»ØµÄÊÇÁ½±íµÄ³Ë»ý£¬Ò²½ÐµÑ¿¨¶û»ý¡£
ÀýÈ磺ÏÂÃæµÄÓï¾ä1ºÍÓï¾ä2µÄ½á¹ûÊÇÏàͬµÄ¡£
Óï¾ä1£ºÒþʽµÄ½»²æÁ¬½Ó£¬Ã»ÓÐCROSS JOIN¡£
SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
from ORDERS O , CUSTOMERS C
WHERE O.ID=1;
Óï¾ä2£ ......

ͨ¹ýSQL´úÂëÊÕËõÊý¾Ý¿â

 
/*------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-16 14:30:23
--  Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
    Jul  9 2008 14:43:34
  &nb ......

Ö彨ÀÏʦµÄSQLÈ«½Ç/°ë½Çת»»

if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[f_convert]')   and   xtype   in   (N'FN',   N'IF',   N'TF'))&n ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ