_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 譴覲給一危一蠍

Contents

1 DELETE覓
2 譟一


1 DELETE覓 #

-- 企 
CREATE TABLE MEMBER( 
	IDX int identity(1,1)
,	NAME Char(20) 
)
GO

-- 一危 
INSERT INTO MEMBER VALUES('A') 
INSERT INTO MEMBER VALUES('A') 
INSERT INTO MEMBER VALUES('B') 
INSERT INTO MEMBER VALUES('B') 
INSERT INTO MEMBER VALUES('B') 
INSERT INTO MEMBER VALUES('B') 
INSERT INTO MEMBER VALUES('C') 
INSERT INTO MEMBER VALUES('C')
GO

--2000危 覯
DELETE FROM MEMBER 
WHERE IDX IN ( 
	SELECT A.IDX as IDX 
	FROM MEMBER A INNER JOIN (
			SELECT 
				MAX(IDX) AS IDX
			,	NAME AS NAME 
			,	COUNT(*)  AS CNT 
			FROM MEMBER 
			GROUP BY NAME 
			HAVING COUNT(*) >1) B
	ON A.NAME = B.NAME  
	AND A.IDX!=B.IDX 
)

--2005伎 覯( 襭覲企   觜朱 豌襴  )
DELETE A
FROM 
(
	SELECT 
		IDX
	,	NAME
	,	ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY IDX) NUM
	FROM MEMBER
) A
WHERE NUM <> 1

2 譟一 #

譴覲 一危郁 れ願 語 螳豌 覓願屋 覦磯覓語企. り骸 覿殊襦 覦 企. 一危一 襷朱 覿伎る SQL覓語企. GROUP BY 蠍壱 譟壱 覦覯 伎 譴覲旧 蟇壱 れ ロ 覦覯 蟆 譬.
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:53

螳る所 谿 蟇磯 語 . ()