sqlµÄ INNER JOIN, left join,right joinÓï·¨
inner join(µÈÖµÁ¬½Ó) Ö»·µ»ØÁ½¸ö±íÖÐÁª½á×Ö¶ÎÏàµÈµÄÐÐ
left join(×óÁª½Ó) ·µ»Ø°üÀ¨×ó±íÖеÄËùÓмǼºÍÓÒ±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
right join(ÓÒÁª½Ó) ·µ»Ø°üÀ¨ÓÒ±íÖеÄËùÓмǼºÍ×ó±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
INNER JOIN Óï·¨£º
INNER JOIN Á¬½ÓÁ½¸öÊý¾Ý±íµÄÓ÷¨£º
SELECT * from ±í1 INNER JOIN ±í2 ON ±í1.×ֶκÅ=±í2.×ֶκÅ
INNER JOIN Á¬½ÓÈý¸öÊý¾Ý±íµÄÓ÷¨£º
SELECT * from (±í1 INNER JOIN ±í2 ON ±í1.×ֶκÅ=±í2.×ֶκÅ) INNER JOIN ±í3 ON ±í1.×ֶκÅ=±í3.×ֶκÅ
INNER JOIN Á¬½ÓËĸöÊý¾Ý±íµÄÓ÷¨£º
SELECT * from ((±í1 INNER JOIN ±í2 ON ±í1.×ֶκÅ=±í2.×ֶκÅ) INNER JOIN ±í3 ON ±í1.×ֶκÅ=±í3.×ֶκÅ) INNER JOIN ±í4 ON Member.×ֶκÅ=±í4.×ֶκÅ
INNER JOIN Á¬½ÓÎå¸öÊý¾Ý±íµÄÓ÷¨£º
SELECT * from (((±í1 INNER JOIN ±í2 ON ±í1.×ֶκÅ=±í2.×ֶκÅ) INNER JOIN ±í3 ON ±í1.×ֶκÅ=±í3.×ֶκÅ) INNER JOIN ±í4 ON Member.×ֶκÅ=±í4.×ֶκÅ) INNER JOIN ±í5 ON Member.×ֶκÅ=±í5.×ֶκÅ
Á¬½ÓÁù¸öÊý¾Ý±íµÄÓ÷¨£ºÂÔ£¬ÓëÉÏÊöÁª½Ó·½·¨ÀàËÆ£¬´ó¼Ò¾ÙÒ»·´Èý°É£º£©
×¢ÒâÊÂÏ
ÔÚÊäÈë×Öĸ¹ý³ÌÖУ¬Ò»¶¨ÒªÓÃÓ¢Îİë½Ç±êµã·ûºÅ£¬µ¥´ÊÖ®¼äÁôÒ»°ë½Ç¿Õ¸ñ£»
ÔÚ½¨Á¢Êý¾Ý±íʱ£¬Èç¹ûÒ»¸ö±íÓë¶à¸ö±íÁª½Ó£¬ÄÇôÕâÒ»¸ö±íÖеÄ×ֶαØÐëÊÇ“Êý×Ö”Êý¾ÝÀàÐÍ£¬¶ø¶à¸ö±íÖеÄÏàͬ×ֶαØÐëÊÇÖ÷¼ü£¬¶øÇÒÊÇ“×Ô¶¯±àºÅ”Êý¾ÝÀàÐÍ¡£·ñÔò£¬ºÜÄÑÁª½Ó³É¹¦¡£
´úÂëǶÌ׿ìËÙ·½·¨£ºÈ磬ÏëÁ¬½ÓÎå¸ö±í£¬ÔòÖ»ÒªÔÚÁ¬½ÓËĸö±íµÄ´úÂëÉϼÓÒ»¸öǰºóÀ¨ºÅ£¨Ç°À¨ºÅ¼ÓÔÚfromµÄºóÃæ£¬ºóÀ¨ºÅ¼ÓÔÚ´úÂëµÄĩβ¼´¿É£©£¬È»ºóÔÚºóÀ¨ºÅºóÃæ¼ÌÐøÌí¼Ó“INNER JOIN ±íÃûX ON ±í1.×ֶκÅ=±íX.×ֶκŔ´úÂë¼´¿É£¬ÕâÑù¾Í¿ÉÒÔÎÞÏÞÁª½ÓÊý¾Ý±íÁË£º£©
inner join on, left join on, right join on½²½â(×ªÔØ)
1.ÀíÂÛ
Ö»ÒªÁ½¸ö±íµÄ¹«¹²×Ö¶ÎÓÐÆ¥ÅäÖµ£¬¾Í½«ÕâÁ½¸ö±íÖеļǼ×éºÏÆðÀ´¡£
¸öÈËÀí½â£ºÒÔÒ»¸ö¹²Í¬µÄ×Ö¶ÎÇóÁ½¸ö±íÖзûºÏÒªÇóµÄ½»¼¯£¬²¢½«Ã¿¸ö±í·ûºÏÒªÇóµÄ¼Ç¼ÒÔ¹²Í¬µÄ×Ö¶ÎΪǣÒýºÏ²¢ÆðÀ´¡£
Óï·¨
from table1 INNER JOIN table2 ON table1 . field1 compopr table2 . field2
INNER JOIN ²Ù×÷°üº¬ÒÔϲ¿·Ö£º
²¿·Ö
˵Ã÷
table1, table2
Òª×éºÏÆäÖеļǼµÄ±íµÄÃû³Æ¡£
field1£¬field2
ÒªÁª½ÓµÄ×ֶεÄÃû³Æ¡£Èç¹ûËüÃDz»ÊÇÊý×Ö£¬ÔòÕâЩ×ֶεÄÊý¾ÝÀàÐͱØÐëÏàͬ£¬²¢ÇÒ°üº¬Í¬ÀàÊý¾Ý£¬µ«ÊÇ£¬ËüÃDz»±Ø¾ßÓÐÏàͬµÄÃû³Æ¡£
compopr
Ïà¹ØÎĵµ£º
¹þÏ£Áª½ÓÊǵÚÈýÖÖÎïÀíÁª½ÓÔËËã·û£¬µ±Ëµµ½¹þÏ£Áª½Óʱ£¬ËüÊÇÈýÖÖÁª½ÓÔËËãÖÐÐÔÄÜ×îºÃµÄ£®Ç¶Ì×Ñ»·Áª½ÓÊÊÓÃÓÚÏà¶Ô½ÏСµÄÊý¾Ý¼¯£¬¶øºÏ²¢Áª½ÓÊÊÓÃÓÚÖеȹæÄ£µÄÊý¾Ý¼¯£¬¶ø¹þÏ£Áª½ÓÔòÊÊÓÃÓÚ´ó¹æÄ£Áª½ÓµÄÊý¾Ý¼¯£®
¹þÏ£Áª½ÓËã·¨²ÉÓ㢹¹½¨£¢ºÍ£¢Ì½²â£¢Á½²½À´Ö´ÐУ®ÔÚ£¢¹¹½¨£¢½×¶Î£¬ËüÊ×ÏÈ´ÓµÚÒ»¸öÊäÈëÖжÁÈ¡ËùÓÐÐУ¨³£½Ð×ö×ó»ò¹¹½¨Ê ......
C#¡¢ASP.NETÖн«Excel±íÖеÄÊý¾Ýµ¼Èëµ½Sql ServerÊý¾Ý¿âÖжÔÓ¦µÄ±íÖС£
ÕâÀïÎÒÃǽ«E:\test.xls
ÖÐSheet1
±íÖеÄÊý¾Ýµ¼Èëµ½testÊý¾Ý¿âÖеÄmy_test
±íÖС£
Èí¼þ£ºVS2005£¬SQL Server2000,
test.xlsÖÐSheet1±íÖÐÓÐÈý¸ö×ֶΣ¬±àºÅ¡¢Ãû³Æ¡¢±¸×¢¡£¾ßÌåÄÚÈÝÈçÏÂͼ¡£
1¡¢
Ê×ÏÈ£¬ÎÒÃÇÒ ......
ÆäʵÄã¿ÉÒÔʹÓÃÊÂÎñ´¦Àí
±È·½ËµÔÚÒ»¸ö×Ö¶ÎÀïÃæÌí¼ÓÒ»¸öboolean µÄ×ֶε±ÄãÒª´¦Àí¸Ã×ֶεÄʱºò¾Í True ÄÄô±ðµÄÈ˶¼²»¿ÉÒÔ½øÐвÙ×÷
¡¡Èç¹ûÊÇFalse ÄÄô¾Í¿ÉÒÔ½øÐвÙ×÷¡«¡«ºÇ¿É¡«¡«ÎÒÊÇÕâÑùµÄ
¡¡¡¡²»¹ýÄã¿ÉÒÔ¿´¿´¡¡
¡¡SQLËø»úÖÆ
¡¡¡¡ÒÔÏÂÊÇÎÒÕÒµÄһЩÁÏ¡¡Èç¹ûÒªÀí½âSQLËø»úÖÆ ×îºÃ°ÑÏÂÀ´¿´ÍêŶ
ĬÈϵÄÊÂÎñ¸ôÀë¼ ......
±¾À´ÊÇmssql+hibernate+native SQL Ó¦ÓõĺܺÍг
µ«Êǵ½Á˰Ñmssql»»³Émysql£¬¾Í³öÁË´í(ͬÑùµÄÊý¾Ý½á¹¹ºÍÊý¾Ý)¡£
²éѯ·½·¨ÊÇ£º
String sql =
"select id XXX_ID from t_tab";
List<Map> list = session.createSQLQuery(sql)
.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
.list();
´í ......
1.Union
UNION Ö¸ÁîµÄÄ¿µÄÊǽ«Á½¸ö SQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£´ÓÕâ¸ö½Ç¶ÈÀ´¿´£¬ UNION ¸ú JOIN ÓÐЩÐíÀàËÆ£¬ÒòΪÕâÁ½¸öÖ¸Áî¶¼¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£ UNION µÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬ÎÒÃÇÖ»»á¿´µ½²»Í¬µÄ×ÊÁÏÖµ (ÀàËÆ SELECT DISTINCT)¡£
UNION µ ......