ɾ³ýÖØ¸´Êý¾ÝµÄsql·½·¨
±íÖÐÓÐһЩ¼Ç¼ÄÚÈÝÖØ¸´£¬Ò²¾ÍÊÇ˵ÕâЩ¼Ç¼³ýÁËID²»Í¬Ö®Í⣬ÆäËûµÄÐÅÏ¢¶¼Ïàͬ¡£ÐèÒª°ÑÖØ¸´µÄ¼Ç¼±£ÁôÒ»Ìõ£¬Ê£ÏµÄɾ³ý
--µÚÒ»ÖÖ·½·¨
delete from temp where id not IN (select min(id) from temp group by col1,col2)
--¶Ôcol1,col2£¬¼´ÒªÉ¾³ýµÄÊý¾Ýcol1,col2Á½¸öÁж¼Ïàͬ,ɾ³ýid´óµÄÐÐ
--µÚ¶þÖÖ·½·¨
with a as
(select row_number() over (partition by col1,col2 order by col1,col2) as rid,* from temp
)
delete a where rid>1
--partition by col1,col2 ×÷ÓÃÈçgroup by col1,col2,¼´ÒªÉ¾³ýµÄÐÐcol1,col2Á½¸öÁеÄÊý¾ÝÏàͬ£¬É¾³ýid´óµÄÐÐ
Ïà¹ØÎĵµ£º
ÔÚwindows2003ÏÂÃæ°²×°SQL Server2000£¬»áÌáʾwindows²»Ö§³ÖSQL Server2000µÄ°æ±¾£¬°²×°ºó£¬ÔÚ±¾»úÊÇ¿ÉÒÔÕý³£Ê¹Óõ쬵«ÊÇÎÞ·¨Á¬½Óµ½ÆäËûSQL Server2000µÄ·þÎñÆ÷£¬Ò²ÎÞ·¨Í¨¹ýÆäËû»úÆ÷ÉϵÄSQL Server2000·ÃÎÊ£¬±ØÐë°²×°SQL Server2000sp3»òsp4²¹¶¡²Å¿ÉÒÔ¡£ÆäʵÓÃSQL Server2005¾ÍûÕâôÂé·³ÁË¡£ ......
DECLARE @temp TABLE(
id INT,
[name] VARCHAR(50),
class VARCHAR(50)
)
INSERT INTO @temp
SELECT 1,'a','A'
UNION ALL SELECT 2,'b','C'
UNION ALL SELECT 3,'c','B'
UNION ALL SELECT 4,'d','C'
UNION ALL SELECT 5,'e','B'
UNION ALL SELECT 6,'f','A'
SELECT * from @temp AS _temp WHERE [name] IN
(
......
Êý¾Ý¿â×Öµä°üÀ¨±í½á¹¹(·Ö2KºÍ2005£©¡¢Ë÷ÒýºÍÖ÷¼ü.Íâ¼ü.Ô¼Êø.ÊÓͼ.º¯Êý.´æ´¢¹ý³Ì.´¥·¢Æ÷.¹æÔò¡£¿ÉÒÔÔÚÆóÒµ¹ÜÀíÆ÷¡¢²éѯ·ÖÎöÆ÷Öмòµ¥Ö´ÐУ¬Ö±½ÓÁ˵±µÄ²é³öSQL2K¼°SQL2005µÄËùÓÐÊý¾Ý×ֵ䣬·½±ãÎĵµµÄ±àд£¬Ï£Íû¶Ô´ó¼ÒÓаïÖú¡£
1. SqlServer2000Êý¾Ý¿â×Öµä--±í½á¹¹.sql
SELECT TOP 100 PERCENT --a.id,
& ......
SQLµÄ»ù±¾¶ÔÏóÖ÷ÒªÓг£Á¿£¬±íʾ·û£¬·Ö¸ô·û£¬±£Áô¹Ø¼ü×Ö¡£
1¡¢³£Á¿
³£Á¿ÊÇÒ»¸ö°üº¬ÎÄ×ÖÓëÊý×Ö£¬Ê®Áù½øÖÆ»òÊý×Ö³£Á¿¡£Ò»¸ö×Ö·û´®³£Á¿°üº¬µ¥ÒýºÅ('')»òË«ÒýºÅ("")×Ö·û¼¯ÖеÄÒ»¸ö»ò¶à¸ö×Ö·û¡£
Èç¹ûÏëÔÚµ¥ÒýºÅ·Ö¸ôµÄ×Ö·û´®ÖÐÓõ½µ¥¶ÀµÄÒýºÅ£¬¿ÉÒÔÔÚÕâ¸ö×Ö·ûÖÐÓû§Á¬ÐøµÄµ¥ÒýºÅ£¨¼´ÓÃÁ ......
The following tables describe certain SQL
limits. Adhering to the most restrictive case can help the programmer
design application programs that are easily portable.
Table 7. Identifier Length Limits
Description
Limit in Bytes
Longest authorization
name (can only be single-byte characters) ......