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

sql server ϵͳº¯ÊýÓ÷¨ÊµÀý

ϵͳº¯Êý
1.case when ... then ..else ..end(ÓÃÓÚ¶ÔÌõ¼þ½øÐвâÊÔ)
e.Select id,case when name='deepwishly' then 'ÀÏ´ó' else 'ÆäËû' end as Type
 ÏÔʾ id  type
          1   ÀÏ´ó
2.cast()/convert() ǰÕß¾ßÓÐANSI SQL-92¼æÈÝÐÔ£¬ºóÕß¹¦ÄܸüÇ¿´ó
e, cast(variable_or_column As datatype);convert(datatype,varialbe_or_column)
3.isdate()ÅжÏÊÇ·ñÊÇÓÐЧµÄÈÕÆÚ»òʱ¼ä£¬²âÊÔÈÕÆÚ¸ñʽ±ØÐëÓëʹÓà set dateformat or set languageÉ趨µÄ¸ñʽ¾ßÓÐÏàͬµÄµØÓò¸ñʽ
e, select isdate(varialbe_or_column)//ÊÇÓÐЧÈÕÆÚ·µ»Ø1·ñÔò·µ»Ø0
4.isnull() ÅжÏÊÇ·ñÊÇnull
isnull(value_to_test,new_value)
e,Ìæ»»Êý¾Ý¿â»ò±äÁ¿ÖÐnullΪijֵ£¬select isnull(variable_or_column,new_value)
5.isnumeric()ÅжÏÊÇ·ñÊÇÊý×Ö ·µ»Ø0£¬false»ò1£¬true
e£¬select(variable_or_colunn)


Ïà¹ØÎĵµ£º

sqlÖÐ in ¡¢not in ¡¢exists¡¢not exists Ó÷¨ºÍ²î±ð

exists £¨sql ·µ»Ø½á¹û¼¯ÎªÕ棩
not exists (sql ²»·µ»Ø½á¹û¼¯ÎªÕ棩
ÈçÏ£º
±íA
ID NAME
1    A1
2    A2
3  A3
±íB
ID AID NAME
1    1 B1
2    2 B2
3    2 B3
±íAºÍ±íBÊÇ£±¶Ô¶àµÄ¹ØÏµ A.ID => B.AID
......

SQL Server¸ù¾Ý²éѯ½á¹û,Éú³ÉXMLÎļþ

SQL Server¸ù¾Ý²éѯ½á¹û,Éú³ÉXMLÎļþ
À´Ô´£º²»Ïê  ×÷ÕߣºØýÃû  Ê±¼ä£º2009-3-6 22:15:58 ·¢²¼£ººÚ¿ÍÈí¼þÔ°
/*
'bcp' ²»ÊÇÄÚ²¿»òÍⲿÃüÁҲ²»ÊÇ¿ÉÔËÐеijÌÐò£¿
¿´¿´ÔÚC:\Program Files\Microsoft SQL Server\80\Tools\BinnÀïÃæÓÐûÓÐbcp.exeÕâ¸öÎļþ
È»ºó¿´¿´pathÀïÃæ¼ÓC:\Program Files\Microso ......

MS SQL ²éѯÁª½ÓÔËËãϵÁУ­£­¹þÏ£Áª½Ó£¨Hash Join)

¹þÏ£Áª½ÓÊǵÚÈýÖÖÎïÀíÁª½ÓÔËËã·û£¬µ±Ëµµ½¹þÏ£Áª½Óʱ£¬ËüÊÇÈýÖÖÁª½ÓÔËËãÖÐÐÔÄÜ×îºÃµÄ£®Ç¶Ì×Ñ­»·Áª½ÓÊÊÓÃÓÚÏà¶Ô½ÏСµÄÊý¾Ý¼¯£¬¶øºÏ²¢Áª½ÓÊÊÓÃÓÚÖеȹæÄ£µÄÊý¾Ý¼¯£¬¶ø¹þÏ£Áª½ÓÔòÊÊÓÃÓÚ´ó¹æÄ£Áª½ÓµÄÊý¾Ý¼¯£®
¹þÏ£Áª½ÓËã·¨²ÉÓ㢹¹½¨£¢ºÍ£¢Ì½²â£¢Á½²½À´Ö´ÐУ®ÔÚ£¢¹¹½¨£¢½×¶Î£¬ËüÊ×ÏÈ´ÓµÚÒ»¸öÊäÈëÖжÁÈ¡ËùÓÐÐУ¨³£½Ð×ö×ó»ò¹¹½¨Ê ......

Sqlº¯Êý´óÈ«

--¾ÛºÏº¯Êý
use pubs
go
select avg(distinct price)  --ËãÆ½¾ùÊý
from titles
where type='business'
go
use pubs
go
select max(ytd_sales)  --×î´óÊý
from titles
go
use pubs
go
select min(ytd_sales) --×îСÊý
from titles
go
use pubs
go
select type,sum(price),sum(advance)  ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ