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.»õÎï±àºÅ
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......
Ö±½ÓÔÚSQL²éѯ·ÖÎöÆ÷ÖжÁÈ¡EXCELÎļþÐèҪʹÓõ½OPENDATASOURCE¡£
µ«ÊÇʹÓÃËü֮ǰÐèÒª½øÐÐÅäÖÃһϡ£¼ÇµÃÈçÏÂÅäÖÃÊDZØÐëµÄ£º
1¡¢Ö´ÐÐÕâÁ½¸ö´æ´¢¹ý³Ì£º
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
ËüµÄ×÷Óãº
µÚÒ»¸öÊÇ£ºÊÇ·ñÖ§³Ö¸ß¼¶Ñ¡ ......
Ê×ÏÈ£¬ÎÒÃÇ¿´¿´existsºÍinµÄЧÂÊÎÊÌ⣬ÕâÀïÎÒֻ˵Ã÷Ò»ÖÖ²âÊÔÓï¾ä
set statistics io on
sqlstatement
set statistics io off¡¢
»òÕß
set statistics time on
sqlstatement
set statistics time off
´ÓstudioÀïÃæµÄÏûÏ¢¿ÉÒÔ¿´³öÎÊÌ⣬ÎÒÒýÓÃÍøÉϵÄһЩ׼Ôòhttp://www.cnblogs.com/diction/arch ......
ÔÚSQL Server ÖвåÈëÒ»ÌõÊý¾ÝʹÓÃInsertÓï¾ä£¬µ«ÊÇÈç¹ûÏëÒªÅúÁ¿²åÈëÒ»¶ÑÊý¾ÝµÄ»°£¬Ñ»·Ê¹ÓÃInsert²»½öЧÂʵͣ¬¶øÇһᵼÖÂSQLһϵͳÐÔÄÜÎÊÌâ¡£ÏÂÃæ½éÉÜSQL ServerÖ§³ÖµÄÁ½ÖÖÅúÁ¿Êý¾Ý²åÈë·½·¨£ºBulkºÍ±íÖµ²ÎÊý(Table-Valued Parameters)¡£
ÔËÐÐÏÂÃæµÄ½Å±¾£¬½¨Á¢²âÊÔÊý¾Ý¿âºÍ±íÖµ²ÎÊý¡£
´úÂëÈçÏÂ:
--Create D ......
TOM´óÊåµÄµ÷ÓÅÊéÖÐ˵¹ýÒ»¾ä»°£¬¾ßÌåÓ¢ÎľÍÍüÁË£¬´ó¸ÅÒâ˼¾ÍÊÇ£ºÈç¹ûÓÐÈËÈÃÎÒд±¾ÔõÑùÈÃORACLEÐÔÄÜ×îÂýµÄÊéµÄ»°ÎһὫȡÏû°ó¶¨±äÁ¿(bind variable)×öΪÊéµÄµÚÒ»ÕºÍ×îºóÒ»ÕÂ(ËûµÄÒâ˼ÊÇËûºÜÓÐÓÄĬ~~!)£¬¿É¼û°ó¶¨±äÁ¿µÄÖØÒªÐÔ¡£
°ó¶¨±äÁ¿´ó¶àÓÃÔÚOLTP(online transaction process)ÖУ¬ÔÚOLAP(online analizy process)ÖоÍû± ......