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

SQL Pivot & UnPivot

create table students (
  name varchar(25),
  class varchar(25),
  grade int
)
insert into students values ('ÕÅÈý','ÓïÎÄ',20)
insert into students values ('ÕÅÈý','Êýѧ',90)
insert into students values ('ÕÅÈý','Ó¢Óï',50)
insert into students values ('ÀîËÄ','ÓïÎÄ',81)
insert into students values ('ÀîËÄ','Êýѧ',60)
insert into students values ('ÀîËÄ','Ó¢Óï',90)
select * from students
pivot(
max(grade)
FOR [class] IN ([ÓïÎÄ],[Êýѧ],[Ó¢Óï])
) AS pvt
/*
ÀîËÄ    81    60    90
ÕÅÈý    20    90    50
*/
--=========================================================================
create table students (
  name varchar(25),
  ÓïÎÄ int,
  Êýѧ int,
  Ó¢Óï int
)
GO
INSERT INTO students values ('ÀîËÄ',81,60,90)
INSERT INTO students values ('ÕÅÈý',20,90,50)
select *
from
students
unpivot
(
grade
for class in
([ÓïÎÄ],[Êýѧ],[Ó¢Óï])
) AS upvt
/*
ÀîËÄ    81    ÓïÎÄ
ÀîËÄ    60    Êýѧ
ÀîËÄ    90    Ó¢Óï
ÕÅÈý    20    ÓïÎÄ
ÕÅÈý    90    Êýѧ
ÕÅÈý    50    Ó¢Óï
*/


Ïà¹ØÎĵµ£º

SQL string to date time

ÔÚSQL serverÖо­³£»áת»»stringµ½datetimeÀàÐÍ£¬×î³£Óõĺ¯Êý¾ÍÊÇConvert¡£ÄÇô¶ÔConvertÕâ¸öº¯Êý£¬²»µÃ²»ÏêϸµÄÑо¿Ò»Ï¡£ConvertÕâ¸öº¯ÊýµÄ¹¦ÄܺÜÇ¿´ó£¬¸ñʽÓֺܼòµ¥CONVERT ( data_type [ ( length ) ] , expression [ , style ] )¡£µ¥¾Í½«stringµ½datetimeÀàÐ͵Äת»»¾ÍÓкܶàÑùʽ¡£Èç: Convert(datetime, expression), ......

sql²»³£¼ûÓï¾ä»ã×Ü

1¡¢join
A±íµÄÖ÷¼üÊÇ×÷ΪB±íµÄÍâ¼ü¡£ÔÚ²éѯµÄʱºò£¬¿ÉÒÔͨ¹ý²»Í¬µÄjoin½«AºÍB±íÁ´½ÓÆðÀ´£¬´Ó¶øµÃµ½²»Í¬µÄ²éѯ½á¹û¡£
* JOIN: Èç¹û±íÖÐÓÐÖÁÉÙÒ»¸öÆ¥Å䣬Ôò·µ»ØÐÐ
* INNER JOIN: Èç¹ûÁ½¸ö±íÖÐÓÐÆ¥ÅäµÄ£¬Ôò·µ»ØÐР         
* LEFT JOIN: ¼´Ê¹ÓÒ±íÖÐûÓÐÆ¥Å䣬Ҳ´Ó×ó± ......

SQL Server 2008 R2 Ó¦Óü°¶à·þÎñÆ÷¹ÜÀí

Ëùν¶à·þÎñÆ÷¹ÜÀí (Multiserver Administration)¾ÍÊÇSQL Server 2008 R2ÌṩµÄ×Ô¶¯¹ÜÀí¶à¸ö SQL Server ʵÀý¹ý³ÌµÄ¹¦ÄÜ¡£ÔÚ¶à·þÎñÆ÷¹ÜÀíÖУ¬Á¬½Óµ½Ö÷·þÎñÆ÷²¢´ÓÆä½ÓÊÕ×÷ÒµµÄ·þÎñÆ÷±»³ÆÖ®ÎªÄ¿±ê·þÎñÆ÷¡£SQL Server 2008 R2ÍÆ³öÁ˹ÜÀíSQL·þÎñÆ÷Êý¾Ý¿âÒýÇæµÄ¶à¸öʵÀýµÄSQL·þÎñÆ÷¹¤¾ß£¬Ê¹ÓÃÕâЩ¹¤¾ß¿ÉÒÔͨ¹ýÖÐÑë·þÎñÆ÷¶Ô¶à¸öSQL ......

¼òµ¥µÄsqlÊý¾Ý¿â´¥·¢Æ÷ʵÀý

--ͨ¹ýsqlÆóÒµ¹ÜÀíÆ÷Ð޸ĺÍɾ³ýa±íÖÐÊý¾Ýʱ»á³öÏÖ´íÎó
--sqlÆóÒµ¹ÜÀíBug£¬Í¨¹ý³ÌÐò»òÖ´ÐÐsqlÓï¾ä¸üÐÂa±íÊý¾ÝûÓÐÎÊÌâ
--Ìí¼Ó
Insert a (FName, FCode, FOther) Values('11','2222','33')
--ÐÞ¸Ä
Update a Set FName='22_Edit' Where FCode='22'
--ɾ³ý
Delete a Where FCode='22'
--²é¿´a/b±íÊý¾Ý
Select * from a ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ