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

SQL SERVER 2000/2005 ÁÐתÐÐ ÐÐתÁÐ


ÆÕͨÐÐÁÐת»»
ÎÊÌ⣺¼ÙÉèÓÐÕÅѧÉú³É¼¨±í(tb)ÈçÏÂ:
ÐÕÃû ¿Î³Ì ·ÖÊý
ÕÅÈý ÓïÎÄ 74
ÕÅÈý Êýѧ 83
ÕÅÈý ÎïÀí 93
ÀîËÄ ÓïÎÄ 74
ÀîËÄ Êýѧ 84
ÀîËÄ ÎïÀí 94
Ïë±ä³É(µÃµ½ÈçϽá¹û)£º
ÐÕÃû ÓïÎÄ Êýѧ ÎïÀí
---- ---- ---- ----
ÀîËÄ 74 84 94
ÕÅÈý 74 83 93
-------------------
*/
create table tb(ÐÕÃû varchar(10) , ¿Î³Ì varchar(10) , ·ÖÊý int)
insert into tb values('ÕÅÈý' , 'ÓïÎÄ' , 74)
insert into tb values('ÕÅÈý' , 'Êýѧ' , 83)
insert into tb values('ÕÅÈý' , 'ÎïÀí' , 93)
insert into tb values('ÀîËÄ' , 'ÓïÎÄ' , 74)
insert into tb values('ÀîËÄ' , 'Êýѧ' , 84)
insert into tb values('ÀîËÄ' , 'ÎïÀí' , 94)
go
--SQL SERVER 2000 ¾²Ì¬SQL,Ö¸¿Î³ÌÖ»ÓÐÓïÎÄ¡¢Êýѧ¡¢ÎïÀíÕâÈýÃſγ̡£(ÒÔÏÂͬ)
select ÐÕÃû as ÐÕÃû ,
max(case ¿Î³Ì when 'ÓïÎÄ' then ·ÖÊý else 0 end) ÓïÎÄ,
max(case ¿Î³Ì when 'Êýѧ' then ·ÖÊý else 0 end) Êýѧ,
max(case ¿Î³Ì when 'ÎïÀí' then ·ÖÊý else 0 end) ÎïÀí
from tb
group by ÐÕÃû
--SQL SERVER 2000 ¶¯Ì¬SQL,Ö¸¿Î³Ì²»Ö¹ÓïÎÄ¡¢Êýѧ¡¢ÎïÀíÕâÈýÃſγ̡£(ÒÔÏÂͬ)
declare @sql varchar(8000)
set @sql = 'select ÐÕÃû '
select @sql = @sql + ' , max(case ¿Î³Ì when ''' + ¿Î³Ì + ''' then ·ÖÊý else 0 end) [' + ¿Î³Ì + ']'
from (select distinct ¿Î³Ì from tb) as a
set @sql = @sql + ' from tb group by ÐÕÃû'
exec(@sql)
--SQL SERVER 2005 ¾²Ì¬SQL¡£
select * from (select * from tb) a pivot (max(·ÖÊý) for ¿Î³Ì in (ÓïÎÄ,Êýѧ,ÎïÀí)) b
--SQL SERVER 2005 ¶¯Ì¬SQL¡£
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + ¿Î³Ì from tb group by ¿Î³Ì
exec ('select * from (select * from tb) a pivot (max(·ÖÊý) for ¿Î³Ì in (' + @sql + ')) b')
---------------------------------
/*
ÎÊÌ⣺ÔÚÉÏÊö½á¹ûµÄ»ù´¡ÉÏ¼ÓÆ½¾ù·Ö£¬×Ü·Ö£¬µÃµ½ÈçϽá¹û£º
ÐÕÃû ÓïÎÄ Êýѧ ÎïÀí Æ½¾ù·Ö ×Ü·Ö
---- ---- ---- ---- ------ ----
ÀîËÄ 74 84 94 84.00 252
ÕÅÈý 74 83 93 83.33 250
*/
--SQL SERVER 2000 ¾²Ì¬SQL¡£
select ÐÕÃû ÐÕÃû,
max(case ¿Î³Ì when 'ÓïÎÄ' then ·ÖÊý else 0 end) ÓïÎÄ,
max(case ¿Î³Ì when 'Êýѧ' then ·ÖÊý else 0 end) Êýѧ,
max(case ¿Î³Ì when 'ÎïÀí' then ·ÖÊý else 0


Ïà¹ØÎĵµ£º

sqlµÄ¼¸¸öÅÅÐòº¯Êý

1ÓÃÓÚÅÅÐòµÄº¯Êý
row_number()
rank()
dense_rank()
ntile(group_number)
ÏÂÃæÁоÙÕâ¸öº¯ÊýµÄÓ÷¨£º
row_number()º¯ÊýÒ»°ãÓÃÓÚ×éÄÚÅÅÐò£¬¶øÆäËûÈý¸öº¯ÊýÊǶԽá¹û¼¯ÅÅÐò
Àý×Ó£º·ÖÒ³ÅÅÐò
<!--×¢ÒâÈ«¾Ö±äÁ¿Ò²ÔÚÕâÀïÉùÃ÷£¬²¢ÓöººÅ¸ô¿ª-->
create proc MyDividePageSort @iRowCount int ,@iPageNo int
AS
< ......

ÈýÖÖSQL·ÖÒ³·¨

±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[ID] int IDENTITY (1,1)
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
Óï¾äÐÎʽ£º 
SELECT TOP Ò³¼Ç¼ÊýÁ¿ *
from ±íÃû
WHERE (ID NOT IN
  (SELECT TOP (ÿҳÐÐÊý*(Ò³Êý-1)) ID
  from ±íÃû
  ORDER BY ID))
  ORDER BY ID
//×Ô¼º»¹¿ÉÒÔ¼ÓÉÏһЩ²éѯ ......

SQLʱ¼äº¯Êý


Sqlʱ¼äº¯Êý
Ò»¡¢sql serverÈÕÆÚʱ¼äº¯Êý
Sql ServerÖеÄÈÕÆÚÓëʱ¼äº¯Êý 
1.  µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä 
    select getdate()  
2. dateadd  ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐµĠdatetime Öµ
    ......

SQL¼¶Áª¸üкͼ¶ÁªÉ¾³ý

alter table ±íÃû
add constraint Ô¼ÊøÃû
foreign key(×Ö¶ÎÃû) references Ö÷±íÃû(×Ö¶ÎÃû)
on delete cascade
Óï·¨£º
Foreign Key
(column[,...n])
references referenced_table_name[(ref_column[,...n])]
[on delete cascade]
[on update cascade]
×¢ÊÍ£º
column:ÁÐÃû
referenced_table_name:Íâ¼ü²Î¿¼µÄÖ÷¼ü± ......

sql xml ÈëÃÅ

*
sql xml ÈëÃÅ:
    --by jinjazz
    --http://blog.csdn.net/jinjazz
   
    1¡¢xml:        ÄÜÈÏÊ¶ÔªËØ¡¢ÊôÐÔºÍÖµ
   
    2¡¢xpath:    ѰַÓïÑÔ£¬ÀàËÆwind ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ