SQLÓï¾ä¼áÐÐתºáÁеÄÒ»ÖÖ·½·¨
¿âλ »õÎï±àºÅ ¿â´æÊý
1 0101 50
1 0102 60
1 0103 50
2 0101 90
2 0103 100
2 0111 30
3 0101 120
3 0102 110
4 0101 11
Ö»Áгö±íÖпâλΪ1¡¢2¡¢3µÄÊý¾Ý£¬¸ñʽÈçÏÂ:
»õÎï±àºÅ ¿âλ1 ¿âλ2 ¿âλ3
0101 50 90 120
0102 60 110
0103 50 100
0111 30
ÇëÎÊÓÃÒ»¾äsqlÓï¾äÔõôʵÏÖ£¿
select a.»õÎï±àºÅ£¬sum(b.¿â´æÊý)£¬sum(c.¿â´æÊý)£¬sum(d.¿â´æÊý)
from stable a
left join (select »õÎï±àºÅ£¬ ¿â´æÊý from stable where ¿âλ=1)b on a.»õÎï±àºÅ£½b»õÎï±àºÅ
left join (select »õÎï±àºÅ£¬ ¿â´æÊý from stable where ¿âλ=2)c on a.»õÎï±àºÅ£½c¡£»õÎï±àºÅ
left join (select »õÎï±àºÅ£¬ ¿â´æÊý from stable where ¿âλ=3)c on a.»õÎï±àºÅ£½d¡£»õÎï±àºÅ
group by a.»õÎï±àºÅ
Ïà¹ØÎĵµ£º
¶ÔÓÚÒ»¸öÊý¾Ý¿âÍêÕû±¸·ÝÀ´Ëµ£¬±¸·ÝµÄËٶȺܴó³Ì¶ÈÉÏÈ¡¾öÓÚÏÂÃæÁ½¸öÒòËØ£º¶Á´ÅÅÌÊý¾Ý¡¢ÈÕÖ¾ÎļþµÄÍÌÍÂÁ¿£¬Ð´´ÅÅÌÊý¾ÝÎļþµÄÍÌÍÂÁ¿¡£
ÏÂͼÊDZ¸·Ý¹ý³ÌÖдÅÅ̵ı仯Çé¿ö£º
¶ÁÍÌÍÂÁ¿
¶ÁÍÌÍÂÁ¿µÄ´óСȡ¾öÓÚ´ÅÅ̶ÁÈ¡Êý¾ÝµÄËÙ¶È£¬¶ø´ÅÅ̶ÁÈ¡µÄËÙ¶ÈÓÖÈ¡¾öÓÚÊý¾ÝÎļþÔÚ´ÅÅÌÖеÄλÖá£Òò´Ë£¬Î»ÓÚ²»Í¬ÅÌ·ûÉϲ»Í¬Êý¾Ý¿âÎļþµÄ¶ÁÈ¡Ë ......
SQL ServerʹÓñê×¼B-Ê÷´æ´¢ÐÅÏ¢¡£
Ë÷ÒýÖмä²ã´ÎÊýÓɱíµÄÐÐÊýºÍË÷ÒýÐеĴóС¾ö¶¨¡£ÈôʹÓÃÒ»¸ö½Ï³¤µÄ¼ü£¨key£©À´´´½¨Ë÷Òý£¬Ò»¸ö·ÖÒ³ÉϾÍÖ»ÄÜÈÝÄɽÏÉÙµÄÌõÄ¿£¬¼´Ê÷µÄ²ã´Î¿ÉÄÜ»áÔö¶à¡£
¶ÔÓÚÈκÎË÷Òý£¨ÎÞÂÛÊǾۼ¯»ò·Ç¾Û¼¯Ë÷Òý£©£¬Ë÷ÒýB-Ê÷µÄÒ¶¼¶½Úµã°üº¬Ë÷ÒýµÄ¼üÖµ£¬²¢¶¼°´ÕÕ¼üµÄ˳ÐòÅÅÁС£¾Û¼¯ºÍ·Ç¾Û¼¯Ë÷ÒýµÄÇø±ðÔÚÓÚÒ¶¼¶½Úµ ......
left join(×óÁª½Ó) ·µ»Ø°üÀ¨×ó±íÖеÄËùÓмǼºÍÓÒ±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
right join(ÓÒÁª½Ó) ·µ»Ø°üÀ¨ÓÒ±íÖеÄËùÓмǼºÍ×ó±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
inner join(µÈÖµÁ¬½Ó) Ö»·µ»ØÁ½¸ö±íÖÐÁª½á×Ö¶ÎÏàµÈµÄÐÐ
¾ÙÀýÈçÏ£º
--------------------------------------------
±íA¼Ç¼ÈçÏ£º
aID¡¡¡¡¡¡¡¡¡¡aNum
1¡¡¡¡¡¡¡¡¡¡a ......
Ò»¡¢ÒòÇéÖÆÒË£¬½¨Á¢“Êʵ±”µÄË÷Òý
½¨Á¢“Êʵ±”µÄË÷ÒýÊÇʵÏÖ²éѯÓÅ»¯µÄÊ×ҪǰÌá¡£
Ë÷Òý£¨index£©Êdzý±íÖ®ÍâÁíÒ»ÖØÒªµÄ¡¢Óû§¶¨ÒåµÄ´æ´¢ÔÚÎïÀí½éÖÊÉϵÄÊý¾Ý½á¹¹¡£µ±¸ù¾ÝË÷ÒýÂëµÄÖµËÑË÷Êý¾Ýʱ£¬Ë÷ÒýÌṩÁ˶ÔÊý¾ÝµÄ¿ìËÙ·ÃÎÊ¡£ÊÂʵÉÏ£¬Ã»ÓÐË÷Òý,Êý¾Ý¿âÒ²Äܸù¾ÝSELECTÓï¾ä³É¹¦µØ¼ìË÷µ½½á¹û£¬µ«Ëæ×űí±ä ......
ÔÎÄ:http://www.blogjava.net/Unmi/archive/2009/01/05/249956.html
ÔÚ Oracle Êý¾Ý¿âÖУ¬ÎÒÃÇͨ³£ÔÚ²»Í¬Êý¾Ý¿âµÄ±í¼ä¼Ç¼½øÐи´ÖÆ»òÇ¨ÒÆÊ±»áÓÃÒÔϼ¸ÖÖ·½·¨£º
1. A ±íµÄ¼Ç¼µ¼³öΪһÌõÌõ·ÖºÅ¸ô¿ªµÄ insert Óï¾ä£¬È»ºóÖ´ÐвåÈëµ½ B ±íÖÐ
2. ½¨Á¢Êý¾Ý¿â¼äµÄ dblink£¬È»ºóÓà create table B as select * from A ......