t sql ¶Á³öÿ×éµÄÇ°n¸ö

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 &