Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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ÔÚ½¨Á¢Óë·þÎñÆ÷µÄÁ¬½Óʱ³ö´íµÄ½â¾ö·½·¨

SQLÔÚ½¨Á¢Óë·þÎñÆ÷µÄÁ¬½Óʱ³ö´íµÄ½â¾ö·½
       ÔÚ½¨Á¢Óë·þÎñÆ÷µÄÁ¬½Óʱ³ö´í¡£ÔÚÁ¬½Óµ½ SQL Server 2005 ʱ£¬ÔÚĬÈϵÄÉèÖÃÏ SQL Server ²»ÔÊÐí½øÐÐÔ¶³ÌÁ¬½Ó¿ÉÄܻᵼÖ´Ëʧ°Ü¡£ (provider: ÃüÃû¹ÜµÀÌṩ³ÌÐò, error: 40 - ÎÞ·¨´ò¿ªµ½ SQL Server µÄÁ¬½Ó)
¸Õ¸Õ°²×°µÄÊý¾Ý¿âϵͳ£¬°´ÕÕÄ ......

SQL SERVER ²é¿´CPUÀûÓÃÂÊ

 --²é¿´CPUÀûÓÃÂÊ
-- CREATE PROCEDURE sp_GetTop10_CPU () AS
BEGIN
  SET NOCOUNT ON
  DECLARE @cinterval char(8)
  DECLARE @interval int
  SET @cinterval = '00:00:10'
  CREATE TABLE #thread
  (
    RUN INT NOT NULL,
    SPI ......

SQL SERVER ²é¿´CPUÀûÓÃÂÊ

 --²é¿´CPUÀûÓÃÂÊ
-- CREATE PROCEDURE sp_GetTop10_CPU () AS
BEGIN
  SET NOCOUNT ON
  DECLARE @cinterval char(8)
  DECLARE @interval int
  SET @cinterval = '00:00:10'
  CREATE TABLE #thread
  (
    RUN INT NOT NULL,
    SPI ......

SQL Substring

 SQL ÖÐµÄ substring º¯ÊýÊÇÓÃÀ´×¥³öÒ»¸öÀ¸Î»×ÊÁÏÖеÄÆäÖÐÒ»²¿·Ö¡£Õâ¸öº¯ÊýµÄÃû³ÆÔÚ²»Í¬µÄ×ÊÁÏ¿âÖв»ÍêÈ«Ò»Ñù£º
MySQL: SUBSTR(), SUBSTRING()
Oracle: SUBSTR()
SQL Server: SUBSTRING()
×î³£Óõ½µÄ·½Ê½ÈçÏ (ÔÚÕâÀïÎÒÃÇÓÃSUBSTR()ΪÀý)£º
SUBSTR(str,pos): ÓÉ<str>ÖУ¬Ñ¡³öËùÓдӵÚ<pos>λÖÿªÊ¼ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ