Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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 ServerºÍOracleµÄ³£Óú¯Êý¶Ô±È

---------Êýѧº¯Êý
1.¾ø¶ÔÖµ
S:select abs(-1) value
O:select abs(-1) value from dual
2.È¡Õû(´ó)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
3.È¡Õû£¨Ð¡£©
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
4.È¡Õû£¨½ØÈ¡£©
S:select cast(-1.002 as int) v ......

LINQ to SQLµÄ²»×ã

LINQ to sqlËäÈ»½«Êý¾Ý¿â²Ù×÷ºÍÒµÎñÂß¼­¸ôÀ뿪À´£¬Ê¹¿ª·¢ÈËÔ±Äܹ»Ê¹Óõ¥Ò»µÄÓïÑÔºÍ֪ʶÄܹ»·½±ãµÄ²Ù×÷Êý¾Ý¿â²¢´¦ÀíÒµÎñÂß¼­¡£µ«ÊÇÕâ±Ï¾¹ÊÇ΢ÈíO/R½â¾ö·½°¸µÄµÚÒ»¸ö°æ±¾£¬Ïà±ÈÏà¶Ô³ÉÊìµÄDataSetÊý¾Ý¼¯½â¾ö·½°¸À´Ëµ£¬ÎÒÃÇ»¹ÊÇ¿ÉÒÔ¿´µ½Ò»Ð©²»×ã¡£
¡¡¡¡Ê×ÏÈ£¬ÎÒÃÇ×¢Òâµ½ËùÓеÄÊý¾ÝʵÌ岢ûÓдÓÒ»¸ö»ùÀàÖÐÅÉÉú£¬ÕâʹµÃ¸ø¿ª·¢Í¨Ó ......

ѧϰSQLÓï¾äÖ®SQLÓï¾ä´óÈ«

¡¡--Óï ¾ä ¹¦ ÄÜ¡¡¡¡--Êý¾Ý²Ù×÷¡¡¡¡SELECT --´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁС¡¡¡INSERT --ÏòÊý¾Ý¿â±íÌí¼ÓÐÂÊý¾ÝÐС¡¡¡DELETE --´ÓÊý¾Ý¿â±íÖÐɾ³ýÊý¾ÝÐС¡¡¡UPDATE --¸üÐÂÊý¾Ý¿â±íÖеÄÊý¾Ý¡¡¡¡--Êý¾Ý¶¨Òå¡¡¡¡CREATE TABLE --´´½¨Ò»¸öÊý¾Ý¿â±í¡¡¡¡DROP TABLE --´ÓÊý¾Ý¿âÖÐɾ³ý±í¡¡¡¡ALTER TABLE --ÐÞ¸ÄÊý¾Ý¿â±í½á¹¹¡¡¡¡CREATE VI ......

SQL ´óÈ« ÐÄÓêÖ®¼Ò

1.°´ÐÕÊϱʻ­ÅÅÐò:
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.Êý¾Ý¿â¼ÓÃÜ:
select encrypt('ԭʼÃÜÂë')
select pwdencrypt('ԭʼÃÜÂë')
select pwdcompare('ԭʼÃÜÂë','¼ÓÃܺóÃÜÂë') = 1--Ïàͬ£»·ñÔò²»Ïàͬ encrypt('ԭʼÃÜÂë')
select pwdencrypt('ԭʼÃÜÂë')
select pw ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ