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

¼¸¸öɾ³ýÖظ´¼Ç¼µÄSQLÓï¾ä

¼¸¸öɾ³ýÖظ´¼Ç¼µÄSQLÓï¾ä
2009-03-02 10:08
±ÈÈçÏÖÔÚÓÐÒ»ÈËÔ±±í £¨±íÃû£ºpeosons£©
ÈôÏ뽫ÐÕÃû¡¢Éí·ÝÖ¤ºÅ¡¢×¡Ö·ÕâÈý¸ö×Ö¶ÎÍêÈ«ÏàͬµÄ¼Ç¼²éѯ³öÀ´
select   p1.*   from   persons   p1,persons   p2   where   p1.id<>p2.id   and   p1.cardid   =   p2.cardid   and   p1.pname   =   p2.pname   and   p1.address   =   p2.address
¿ÉÒÔʵÏÖÉÏÊöЧ¹û£®
¼¸¸öɾ³ýÖظ´¼Ç¼µÄSQLÓï¾ä
1.ÓÃrowid·½·¨
2.ÓÃgroup by·½·¨
3.ÓÃdistinct·½·¨
1¡£ÓÃrowid·½·¨
¾Ý¾Ýoracle´øµÄrowidÊôÐÔ£¬½øÐÐÅжϣ¬ÊÇ·ñ´æÔÚÖظ´,Óï¾äÈçÏ£º
²éÊý¾Ý:
     select * from table1 a where rowid !=(select   max(rowid)
     from table1 b where a.name1=b.name1 and a.name2=b.name2......)
ɾÊý¾Ý£º
    delete   from table1 a where rowid !=(select   max(rowid)
     from table1 b where a.name1=b.name1 and a.name2=b.name2......)
2.group by·½·¨
²éÊý¾Ý:
¡¡¡¡select count(num), max(name) from student --ÁгöÖظ´µÄ¼Ç¼Êý£¬²¢ÁгöËûµÄnameÊôÐÔ
¡¡¡¡group by num
¡¡¡¡having count(num) >1 --°´num·Ö×éºóÕÒ³ö±íÖÐnumÁÐÖظ´£¬¼´³öÏÖ´ÎÊý´óÓÚÒ»´Î
ɾÊý¾Ý£º
¡¡¡¡delete from student
¡¡¡¡group by num
¡¡¡¡having count(num) >1
¡¡¡¡ÕâÑùµÄ»°¾Í°ÑËùÓÐÖظ´µÄ¶¼É¾³ýÁË¡£
3.ÓÃdistinct·½·¨ -¶ÔÓÚСµÄ±í±È½ÏÓÐÓÃ
create table table_new as   select distinct *   from table1 minux
truncate table table1;
insert into table1 select * from table_new;
²éѯ¼°É¾³ýÖظ´¼Ç¼µÄ·½·¨´óÈ«
1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´Åжϣ¬Ö»ÁôÓÐrowid×îСµÄ¼Ç¼
delete from people
where peopleId in (select peopleId from people group by peop


Ïà¹ØÎĵµ£º

ÔÚWindows XP Ï°²×°SQL SERVER2005Ïêϸ²½Öè¹ý³Ìͼ½â

°²×°Ç°×¼±¸£º
Ç°ÑÔ£º
¡¡¡¡[ѧϰSQL SERVER 2005ϵÁÐ]×¼±¸°Ñѧϰ2005µÄһЩÐĵÃÕûÀí³öÀ´£¬ºÍ´ó¼Ò·ÖÏí£¬¹²Í¬Ñ§Ï°Ò»ÆðÌá¸ß¡£
°²×°×¼±¸£º
1¡¢SQL Server 2005 µÄ¸÷°æ±¾Ö®¼äÑ¡Ôñ
¡¡¡¡´ó¶àÊýÆóÒµ¶¼ÔÚÈý¸ö SQL Server °æ±¾Ö®¼äÑ¡Ôñ£ºSQL Server 2005 Enterprise Edition¡¢SQL Server 2005 Standard Edition ºÍ SQL Server 2 ......

sql(left join,right jion,inner join)Á¬½ÓÏê½â

 ¹ØÓÚsqlÁ¬½Ó
Á¬½ÓÀàÐÍ
ÔÚ¹Øϵ´úÊýÖУ¬Á¬½ÓÔËËãÊÇÓÉÒ»¸öµÑ¿¨¶û»ýÔËËãºÍÒ»¸öÑ¡È¡ÔËËã¹¹³ÉµÄ¡£Ê×ÏÈÓõѿ¨¶û»ýÍê³É¶ÔÁ½¸öÊý¾Ý¼¯ºÏµÄ³ËÔËË㣬Ȼºó¶ÔÉú³ÉµÄ½á¹û
¼¯ºÏ½øÐÐÑ¡È¡ÔËË㣬ȷ±£Ö»°Ñ·Ö±ðÀ´×ÔÁ½¸öÊý¾Ý¼¯ºÏ²¢ÇÒ¾ßÓÐÖصþ²¿·ÖµÄÐкϲ¢ÔÚÒ»Æð¡£Á¬½ÓµÄÈ«²¿ÒâÒåÔÚÓÚÔÚˮƽ·½ÏòÉϺϲ¢Á½¸öÊý¾Ý¼¯ºÏ£¨Í¨³£ÊÇ±í£©£¬²¢²úÉú
Ò ......

SQL ServerÖÐbitÊý¾ÝÀàÐÍÓ¦ÓÃ

ʹÓùý Access µÄÅóÓѶ¼ÖªµÀ£¬Access ÀïÓиö“ÊÇ/·ñ”Êý¾ÝÀàÐÍ£¬ÔÚ SQL Server ÖУ¬Ã»ÓÐÕâ¸öÃû³Æ£¬¶øʹÓà bit¡£ÔÚʹÓùý³ÌÖÐÓм¸µãÐèҪעÒâµÄ£º
£±¡¢bit Óë SQL Server µÄÕûÐͺÍÊý×ÖÊý¾ÝÏàËƵÄÊý×ÖÊý¾Ý£¬µ« bit ÁÐÖ»ÄÜ´æ´¢ 0 ºÍ 1¡£
£²¡¢ÔÚ²åÈë¡¢ÐÞ¸Ä bit Êý¾Ýʱ£¬Ê¹Óà 0 »ò 1 £¬¶ø²»ÊÇ false »ò true£¬bit ×Ö¶ ......

sql ±íÐÞ¸ÄÁÐ

 create table #a
(
a int identity(1,1) primary key,
b int default(0) not null,
c nvarchar(20)
)
insert into #a(c)
select 'a' union all
select 'b' union all
select 'c' union all
select 'd' union all
select 'e'
select * from #a
alter table #a drop constraint DF__#a_____________b__12 ......

sqlÖн«×Ö¶Îת³ÉDecimal


update CHELIANG_MINGDAN set clmd_yunxuzaizhong = cast(clmd_yunxuzaizhong/1000 as decimal(14,4)) where clmd_yunxuzaizhong is not null
update CHELIANG_MINGDAN set clmd_carweight = cast(clmd_carweight/1000 as decimal(14,4)) where clmd_carweight is not null ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ