Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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


Ïà¹ØÎĵµ£º

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 ......

Sql½á´æÓà

select ÐÕÃû,סַ,ÆÚ³õÓà¶î=isnull(ÆÚ³õÔö¼Ó,0)-isnull(ÆÚ³õ¼õÉÙ,0),±¾ÆÚÔö¼Ó,±¾ÆÚ¼õÉÙ,
±¾ÆÚ½áÓà=(isnull(ÆÚ³õÔö¼Ó,0)-isnull(ÆÚ³õ¼õÉÙ,0)+isnull(±¾ÆÚÔö¼Ó,0)-isnull(±¾ÆÚ¼õÉÙ,0)) from (
select ÐÕÃû,סַ,
ÆÚ³õÔö¼Ó=(select ÆÚ³õÔö¼Ó=sum(Ôö¼Ó»ý·Ö) from b where ·¢ÉúÈÕÆÚ<'2006-5-1' and ¿¨ºÅ=a.¿¨ºÅ),
ÆÚ³õ¼õÉ ......

SQlÓαê

declare @CausName as char(20) declare @causenameid as int declare
@Description as varchar(200) declare @Caus_Id as int
declare @sign as char(20)
create table #tb ( Caus_Id int,CausName varchar(20),causNameid int,Caus_Description varchar(200),±êÖ¾ varchar(20))
 
DECLARE myCursor1 CURSOR F ......

SQl×Ô¶¯±¸·Ýɾ³ý

BackupEveryDay
ÿÌì½øÐÐÊý¾Ý¿âµÄ²îÒ챸·Ý
day
Declare @File Varchar(2000)
Set @File='E:\Databasebackup\njyc_data_diff.BAK'
Backup database njyc_data to Disk=@File with DIFFERENTIAL
WeekBackup
ÿÖܽøÐÐÒ»´ÎÊý¾Ý¿âµÄÍêÈ«±¸·Ý£¬±¸·ÝÎļþÃûΪµ±ÌìÈÕÆÚ £¨njyc_data_Äê_ÔÂ_ÈÕ£©
BackupAll
DECLARE @BackupFi ......

Linq²éѯ DataSet ÄÜ·ñºÍSQL ¼¶Áª£¿

        ×î½ü×öÏîÄ¿µÄʱºò£¬Óöµ½ÁËÒ»¸öÎÊÌâ¡£ÎÒÖ÷ÒªÊÇ×öÒ»¸öWeb Services¸ø±ðÈËÓõġ£±ðÈË´«Ò»¸öÓû§IDºÅ¹ýÀ´£¬È»ºóÎÒ½«Õâ¸öÓû§µÄËùÓкÃÓѵÄÏÂÔØ¼Ç¼°ü×°³ÉÒ»¸öDataSet·µ»ØÈ¥¡£ ¶ø¸ù¾ÝÓû§IDºÅ»ñÈ¡¸ÃÓû§µÄËùÓкÃÓÑÐÅÏ¢£¬ÔòÊÇͨ¹ýÁíÒ»¸öWeb ServicesµÃµ½µÄ£¬ÕâÀïΪFriendDS¡£
  ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ