SQL ²é詢²»區·Ö´óС寫

SQL ²é詢²»區·Ö´óС寫
2007Äê04ÔÂ19ÈÕ ÐÇÆÚËÄ 15:27
Õý³£ÓÃÕâ¸ö·½·¨¾Í¸ø達µ½Ð§¹ûÁË¡£select * from
pl_account     where UPPER(fname) like 'PE%'
¸½¼Ó£º
ÔÚsql2000ºÍ7.0µÄ²éѯÓï¾äÖÐ,Çø·Ö´óдµÄ²éѯ·½·¨
--sql2000,¾ÍÓÃÏÂÃæµÄ·½·¨.
--¾ÍÊÇÔÚ×Ö¶ÎÃûºó¼Ó collate Chinese_PRC_CS_AS_WS
--Çø·Ö´óСд¡¢È«°ë½Ç×Ö·ûµÄ·½·¨
--²âÊÔÊý¾Ý
create table ±í(fd varchar(10))
insert into ±í
select aa=@#aa@#
union all select @#Aa@#
union all select @#£Á£Á@#    --È«½Ç£Á
union all select @#£Á,£Á@#   --È«½Ç£Á,°ë½Ç,
union all select @#£Á£¬£Á@# --È«½Ç£Á,È«½Ç£¬
go
--²éѯ
--1.²é´óд×Öĸ
select * from ±í
where fd collate Chinese_PRC_CS_AS_WS like @#%A%@#  
--¾ÍÊÇÔÚ×Ö¶ÎÃûºó¼Ó collate Chinese_PRC_CS_AS_WS
--2.²éÈ«½Ç
select * from ±í
where fd collate Chinese_PRC_CS_AS_WS like @#%£Á%@#
--3.²é°ë½Ç
select * from ±í
where fd collate Chinese_PRC_CS_AS_WS like @#%,%@#
go
--ɾ³ý²âÊÔÊý¾Ý
drop table ±í
/*--²âÊÔ½á¹û
1.²éѯ´óд×ÖĸµÄ½á¹û
fd         
----------
Aa
2.²éѯȫ½Ç×Ö·ûµÄ½á¹û
fd         
----------
£Á£Á
£Á,£Á
£Á£¬£Á
3.²éѯ°ë½Ç×Ö·ûµÄ½á¹û
fd         
----------
£Á,£Á
£¨ËùÓ°ÏìµÄÐÐÊýΪ 1 ÐУ©
--*/
================================================================
--sql7.0,¾ÍÓÃÏÂÃæµÄ·½·¨.
--Èç¹ûÊÇÈ«²¿±È½Ï
--ÏÂÃæÊDzâÊÔ
select * from(
select fd=@#a@#
union all select @#A@#
) a
where cast(fd as varbinary(8000))=cast(@#A@# as varbinary(8000))
/*--²âÊÔ½á¹û
fd   
----
A
£¨ËùÓ°ÏìµÄÐÐÊýΪ 1 ÐУ©
--*/
--Èç¹ûÊDz¿·ÖÆ¥Åä,¾ÍÓÃcharindex:
--ÏÂÃæÊDzâÊÔ
select * from(
select fd=@#a@#
union all select @#A@#
union all select @#aAaa@#
union all select @#aaaa@#
union all select @#cccA@#
) a
where charindex(cast(@#A@# as varbinary(8000)),cast(fd as
varbinary(8000)))>0
/*--²âÊÔ½á¹û
fd   
----
A
aAaa
cccA
£¨ËùÓ°ÏìµÄÐÐÊ