http://topic.csdn.net/u/20100113/17/b52a1ef4-54a8-4333-8119-a161869c1eef.html
---------------------------------
-- Author: liangCK СÁº
-- Title : ²éÿ¸ö·Ö×éÇ°NÌõ¼Ç¼
-- Date : 2008-11-13 17:19:23
---------------------------------
--> Éú³É²âÊÔÊý¾Ý: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)
INSERT INTO #T
SELECT '001',1,'×Þ½¨','ÉîÈëdz³öSQLServer2005¿ª·¢¹ÜÀíÓëÓ¦ÓÃʵÀý','2008-05-10' UNION ALL
SELECT '002',1,'ºú°Ù¾´','SQLServer2005ÐÔÄܵ÷У','2008-03-22' UNION ALL
SELECT '003',1,'¸ñÂÞ·òGroff.J.R.','SQLÍêÈ«ÊÖ²á','2009-07-01' UNION ALL
SELECT '004',1,'KalenDelaney','SQLServer2005¼¼ÊõÄÚÄ»´æ´¢ÒýÇæ','2008-08-01' UNION ALL
SELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL±¦µä','2007-10-05' UNION ALL
SELECT '006',2,'·É˼¿Æ¼¼²úÆ·Ñз¢ÖÐÐÄ','SQLServer2000¸ß¼¶¹ÜÀíÓ뿪·¢','2007-09-10' UNION ALL
SELECT '007',2,'ºú°Ù¾´','SQLServer2005Êý¾Ý¿â¿ª·¢Ïê½â','2008-06-15' UNION ALL
SELECT '008',3,'³ÂºÆ¿ü','SQLServer2000´æ´¢¹ý³ÌÓëXML±à³Ì','2005-09-01' UNION ALL
SELECT '009',3,'ÕÔËÉÌÎ','SQLServer2005ϵͳ¹ÜÀíʵ¼','2008-10-01' UNION ALL
SELECT '010',3,'»ÆÕ¼ÌÎ','SQL¼¼ÊõÊÖ²á','2006-01-01'
--SQL²éѯÈçÏÂ:
--°´GID·Ö×é,²éÿ¸ö·Ö×éÖÐDate×îеÄÇ°2Ìõ¼Ç¼
--1.×Ö¶ÎIDΨһʱ:
SELECT * from #T AS T WHERE ID IN(SELECT TOP 2 ID from #T WHERE GID=T.GID ORDER BY Date DESC)
--2.Èç¹ûID²»Î¨Ò»Ê±:
SELECT * from #T AS T WHERE 2>(SELECT COUNT(*) from #T WHERE GID=T.GID AND Date>T.Date)
--SQL Server 2005 ʹÓÃз½·¨
--3.ʹÓÃROW_NUMBER()½øÐÐÅÅλ·Ö×é
SELECT ID,GID,Author,Title,Date
from
(
SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),*
from #T
) AS T
WHERE rid<=2
--4.ʹÓÃAPPLY
SELECT DISTINCT b.*
from #T AS a
CROSS APPLY
(
SELECT TOP(2) * from #T WHERE a.GID=GID ORDER BY Date DESC
) AS b
--½á¹û
/*
ID GID Author &
ΪÁË´ó¼Ò¸üÈÝÒ×Àí½âÎÒ¾Ù³öµÄSQLÓï¾ä£¬±¾Îļٶ¨ÒѾ½¨Á¢ÁËÒ»¸öѧÉú³É¼¨¹ÜÀíÊý¾Ý¿â£¬È«ÎľùÒÔѧÉú³É¼¨µÄ¹ÜÀíΪÀýÀ´ÃèÊö¡£
¡¡¡¡1.ÔÚ²éѯ½á¹ûÖÐÏÔʾÁÐÃû£º
¡¡¡¡a.ÓÃas¹Ø¼ü×Ö£ºselect name as 'ÐÕÃû' from students order by age
¡¡¡¡b.Ö±½Ó±íʾ£ºselect name 'ÐÕÃû' from students order by age
¡¡¡¡2.¾«È·²éÕÒ:
¡¡¡¡a.ÓÃ ......
ÁÙʱ±íÓëÒ»°ãµÄ±í²»Í¬£¬ËüÊDZ£´æµ½tempDb±íÖС£ÁÙʱ±íµÄ±íÃûÓëÄãËù½¨µÄ±íÃûÒ²²»Ò»Ñù£¬ÒòΪËûҪΪ²»Í¬È˵ÄÏàͬ²Ù×÷´´½¨²»Í¬µÄÁÙʱ±í¡£
1¡¢´íÎóµÄɾ³ý²Ù×÷£º
--´íÎóµÄÁÙʱ±íɾ³ý²Ù×÷£¬ÒòΪËùÔÚÊý¾Ý¿â²»Í¬
IF EXISTS (SELECT * from sysobjects WHERE object_id = OBJE ......