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

SQL ServerÖÐpivot and unpivotµÄÓ÷¨ £¨ÐÐÁл¥×ª£©

 .PivotµÄÓ÷¨Ìå»á:
Óï¾ä·¶Àý:
select PN,[2006/5/30] as [20060530],[2006/6/2] as [20060602]
from consumptiondata a
Pivot (sum(a.M_qty) FOR a.M_date in ([2006/5/30],[2006/6/2])) as PVT 
order by PN
 
Table½á¹¹ Consumptiondata (PN,M_Date,M_qty)
order by PN¿ÉÒª¿É²»Òª,²¢²»ÖØÒª,Ö»ÊÇÅÅÐòµÄ×÷ÓÃ
 
¹Ø¼üµÄÊǺìÉ«²¿·Ö,½âÎöÈçÏÂ,select ´ó¼Ò¶¼ÖªµÀ,PNÊÇ ConsumptionData±íÖеÄÒ»¸öColumn,
[2006/5/30]Ò²ÊÇÒ»¸öColumn,ËûÐèÒªÏÔʾ³É[20060530],×¢Òâ[2006/5/30]²»ÊÇÒ»¸öValue,¶øÊÇÒ»¸öColumn.[2006/6/2]Óë[2006/5/30]Ò»Ñù.
Pivot ( ........... ) as PVTÕâ¸ö½á¹¹Êǹ̶¨¸ñʽ,ûÓÐʲôÐèÒªÌØÊâ˵Ã÷µÄ,µ±È»PVTËæ±ãÄã¸øËûÒ»¸ö NICKNAME ,it doesn't make any differences.
sum(a.M_qty) ÊÇÎÒÃÇÏ£ÍûÏÔʾ³öÀ´µÄÖµ,×¢ÒâÕâ¸öµØ·½±ØÐëÓûã×ܺ¯Êý,·ñÔòÓï·¨²»»á¹ý.
FOR a.M_date in ([2006/5/30],[2006/6/2])for ±íʾ»ã×ܵÄÖµÒªÏÔʾÔÚÄÄÒ»¸öColumnÏÂÃæ
Èç¹ûÎÒÃÇÏëÈÃSum(M_qty)ÏÔʾÔÚPNת»»µÄColumnÏÂÃæ,Ôò¿ÉдΪFor PN, in µÄÇåµ¥±íʾÎÒÃǹØ×¢ÄÄЩҪ²é¿´µÄColumn,×¢ÒâÔÙ´ÎÇ¿µ÷ÊÇColumn,²»ÊÇValue. inµÄÇåµ¥ÊÇColumnÇåµ¥,²»ÊÇValueÇåµ¥,ÊÇM_dateµÄValueת»»³ÉµÄColumnÇåµ¥. 
 
2.UnPivot
--´Ë¶Î¿ÉÒÔÖ±½ÓÔÚSql 2005ÖÐÖ´ÐÐ
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--select * from PVT
--Unpivot the table.
SELECT VendorID, Employee, Orders
from PVT
UNPIVOT (
 Orders FOR Employee IN ([Emp1], [Emp2], [Emp3], [Emp4], [Emp5])
)AS unpvt
GO


Ïà¹ØÎĵµ£º

º½¿Õ¹«Ë¾¹ÜÀíϵͳ(VC++ ÓëSQL 2005)

ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
      ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃ棬ºóÃæ ......

ASP.Net + SQL Server ´æ´¢¹ý³ÌʵÏÖ·ÖÒ³ÅÅÐò

ÎÊÌâÌá³ö:
ÔÚÓ¦ÓóÌÐòÖо­³£ÐèÒª²éѯÊý¾Ý¡£µ±²éѯ½á¹ûÊý¾ÝÁ¿±È½Ï´óµÄʱºò£¬¼ìË÷½á¹û¡¢½çÃæÏÔʾ¶¼ÐèÒª»¨·Ñ´óÁ¿µÄʱ¼ä¡£ÎªÁ˱ÜÃâÕâ¸öÎÊÌ⣬Ӧ¸Ãÿ´ÎÖ»¼ìË÷²¿·ÖÊý¾Ý£¬Ò²¾ÍÊÇʹÓó£¼ûµÄ·ÖÒ³·½Ê½À´´¦Àí¡£·ÖÒ³µÄÎÊÌâÔÚasp.netÖкÃÏñ·Ç³£¼òµ¥£¬Ö»ÒªÔÚGridViewÖÐÆôÓ÷ÖÒ³¾Í¿ÉÒÔÁË¡£ÆôÓ÷ÖÒ³ºó£¬GridView¹ØÁªÊý¾ÝÔ´¿Ø¼þ£¬ÒÀ¾É»á¼ÓÔØ ......

SQL Union ÓëUnion AllÏê½â

1.Union
UNION Ö¸ÁîµÄÄ¿µÄÊǽ«Á½¸ö SQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£´ÓÕâ¸ö½Ç¶ÈÀ´¿´£¬ UNION ¸ú JOIN ÓÐЩÐíÀàËÆ£¬ÒòΪÕâÁ½¸öÖ¸Á¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£ UNION µÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬ÎÒÃÇÖ»»á¿´µ½²»Í¬µÄ×ÊÁÏÖµ (ÀàËÆ SELECT DISTINCT)¡£
UNION µ ......

SQLÊý¾Ý¿â±í»µÁ˵ÄÊÖ¶¯»Ö¸´·½·¨

      ½ñÌì½Óµ½¿Í»§µç»°£¬Ëµ²Ù×÷Êý¾ÝÎÞ·¨±£´æ¡£¾­¹ý·ÖÎö£¬·¢ÏÖËûµÄÊý¾Ý¿âÒѾ­ÓÐ5G¶àµÄ´óС£¬¶ø×îÖÕ·¢ÏÖÓÐÕűíµÄË÷Òý³ö´íÁË£¬ÓÃDBCC CHECKÒ²ÎÞÁ¦»ØÌì¡£
ÿ´ÎÓÃselect * from ln003082 Óï¾ä²éѯ£¬¶¼±¨ÈçÏ´íÎó£º
·þÎñÆ÷: ÏûÏ¢ 605£¬¼¶±ð 21£¬×´Ì¬ 1£¬ÐÐ 1
ÊÔͼ´ÓÊý¾Ý¿â 'ln_fl0125' ÖÐÌáÈ¡µÄÂß¼­ ......

ʹÓà SQL Server ʱÐèÒª¾­³£Óõ½µÄ¼¸¸öÉèÖÃÑ¡Ïî


1. SET DEADLOCK_PRIORITY
˵Ã÷£º¿ØÖÆÔÚ·¢ÉúËÀËøÇé¿öʱ»á»°µÄ·´Ó¦·½Ê½¡£Èç¹ûÁ½¸ö½ø³Ì¶¼Ëø¶¨Êý¾Ý£¬²¢ÇÒÖ±µ½ÆäËü½ø³ÌÊÍ·Å×Ô¼ºµÄËøʱ£¬Ã¿¸ö½ø³Ì²ÅÄÜÊÍ·Å×Ô¼ºµÄËø£¬¼´·¢ÉúËÀËøÇé¿ö¡£
Óï·¨£ºSET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }
²ÎÊý£ºLOW  Ö¸¶¨µ±Ç°»á»°ÎªÊ×Ñ¡ËÀËøÎþÉüÆ·¡£Microsoft® S ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ