SQL ÈçºÎɾ³ýÊý¾Ý±íÖÐÖØ¸´µÄÊý¾Ý£¿
¡¾ÒýÓãºÃÍáï¼¼ÊõÎÄÕÂÕªÒª
¡¿
¾²âÊÔ£¬·½·¨¶þ¿É³É¹¦É¾³ýÊý¾Ý£¬·½·¨Ò»¡¢Èý ɾ³ýÊý¾Ýʧ°Ü¡£Çë·¹ýµÄÅóÓÑ£¬Ö¸µãÃÔ½ò¡£¡£¡£
ÎÊÌ⣺һ¸ö±íÓÐ×ÔÔöµÄID
ÁУ¬±íÖÐÓÐһЩ¼Ç¼ÄÚÈÝÖØ¸´£¬Ò²¾ÍÊÇ˵ÕâЩ¼Ç¼³ýÁËID
²»Í¬Ö®Í⣬ÆäËûµÄÐÅÏ¢¶¼Ïàͬ¡£ÐèÒª°ÑÖØ¸´µÄ¼Ç¼±£ÁôÒ»Ìõ£¬Ê£ÏµÄɾ³ý
·½·¨Ò»£º»¹ÊÇ2000
ÄêµÄʱºòһλOracle
DBA
½Ðm.l·¢¸ø¼¼Êõ²¿È«ÌåµÄ£¨¿ÉϧÔʼÓʼþÕÒ²»µ½ÁË£¬Òª²»È»ÎÒµ±ÎÄÎï·¢¸ø´ó¼Ò£©£º
delete from Score
where [sid] not in (
select min([sid])
from Score
group by [sid],[sname],[score])
·½·¨¶þ£ºz.benÔÚÍøÂçÉÏËѳöÀ´µÄ£º
--
ɾ³ýÏàͬ³ÇÊÐϵÄÏàͬÐÐÕþÇø
delete a from Score a
where a.sid>(
select min(sid) from Score b
where a.sname=b.sname and a.score=b.score)
·½·¨Èý£ºÊ¹ÓÃsql
2005
ÐÂÔöµÄrow_number()
¹¦ÄܺÍwith
¹Ø¼ü×Ö£¬ÎÒÊÇ´ÓÕÔÁ¢¶«ÄÇÀïѧÀ´µÄ¡£
print('ɾ³ýScore±íÖÐÖØ¸´µÄ¼Ç¼')
;WITH a AS (
SELECT ROW_NUMBER() OVER (PARTITION BY [sid],[sname],[score]
ORDER BY [sid],[sname],[score]) AS rn,*
from Score
)
delete from a WHERE a.rn>1
------------------------------------------------------------------------------
¸½Â¼£ºÊý¾Ý¿â±í½Å±¾
USE [DBtest]
GO
/****** Object: Table [dbo].[Score] Script Date: 01/13/2010 23:47:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Score](
[sid] [int] IDENTITY(1,1) NOT NULL,
[sname] [nchar](10) NOT NULL,
[score] [nchar](10) NOT NULL,
CONSTRAINT [PK_Score] PRIMARY KEY CLUSTERED
(
[sid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Êý¾ÝÌî³ä£º
1
aa
bb
2
bb
cc
3
cc
dd
Ïà¹ØÎĵµ£º
Ö´ÐÐÊý¾Ý²Ù×÷ʱ£¬ÓÉÓÚÆ´½ÓSQL´æÔÚÖÖÖÖ±×¶Ë£¬Ôç¾ÍÓ¦¸ÃÅׯúÁË£¬µ«ÔÚÏÖʵ¿ª·¢Ê±£¬ÓÖÓÉÓÚÖÖÖÖÔÒò£¬¹«Ë¾Ò»Ö±²ÉÓÃÕâÖÖ·½Ê½£¨UI²ãºÍÂß¼²ã¶¼ÓÐÑϸñµÄ¹ýÂË£¬µ¹Ò²Ã»³öÏÖ¹ýʲôÎÊÌ⣩£¬µ«×òÌ쿪·¢Ê±È´³öÏÖÁËÒâÏë²»µ½µÄÎÊÌ⣬һ¸ö¼òµ¥µÄÓï¾ä»áÔì³ÉÑÏÖØºó¹û¡£¼òµ¥µÄÓï¾äʾÀýÈçÏ£º
/// <summary>
&nb ......
--½áºÏsys.indexesºÍsys.index_columns,sys.objects,sys.columns²éѯË÷ÒýËùÊôµÄ±í»òÊÓͼµÄÐÅÏ¢
select
o.name as ±íÃû,
i.name as Ë÷ÒýÃû,
c.name as ÁÐÃû,
i.type_desc as ÀàÐÍÃèÊö,
is_primary_key as Ö÷¼üÔ¼Êø,
is_unique_constraint as Î¨Ò»Ô¼Êø,
is_disable ......
Ñ¡Ôñ×Ô CSDN
SQLÓï¾äµ¼Èëµ¼³ö´óÈ«
/******* µ¼³öµ½excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'
/*********** µ¼ÈëExcel
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\tes ......
1.sp_helpsort
SELECT SERVERPROPERTY ('Collation')
²é¿´ÄãµÄÅÅÐò¹æÔò.
²»¹ýÄãµÄÕâ¸öÓ¦¸ÃºÍ×Ö·û¼¯ÓйØ.
2.¸ü¸Ä·þÎñÆ÷ÅÅÐò¹æÔò
¸ü¸Ä SQL Server 2005 ʵÀýµÄĬÈÏÅÅÐò¹æÔòµÄ²Ù×÷¿ÉÄÜ»á±È½Ï¸´ÔÓ£¬°üÀ¨ÒÔϲ½Ö裺
È·±£¾ßÓÐÖØÐ´´½¨Óû§Êý¾Ý¿â¼°ÕâЩÊý¾Ý¿âÖеÄËùÓжÔÏóËùÐèµÄÈ«²¿ÐÅÏ¢»ò½Å±¾¡£
ʹÓù¤¾ß£¨ÀýÈç´óÈÝÁ ......
Sql ServerÖеÄÈÕÆÚÓëʱ¼äº¯Êý
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
select dateadd(day,2,'2004-10-15') --·µ»Ø£º2004-10-17 00:00:00.000
......