星期五, 10月 24, 2008

(題目)把分類的最新一筆抓出來

昨天被人家問道的一個問題..拿出來給大家討論一下..

題目:

有一個"分類"的table..一個"文章" table..
"文章"的table有一欄,是"分類id"..那麼,我若要顯示每一類的最新的那一筆
這段程式怎麼寫..

dfdcccccsdfas

他們的關係圖大概就是類是上面這樣..

t-sql解法

SELECT         dbo._b.aId, dbo._b.bId, dbo._a.aName, dbo._b.bName,
dbo._b.bTime
FROM dbo._a INNER JOIN
dbo._b ON dbo._a.aId = dbo._b.aId
WHERE ((CONVERT(nvarchar, dbo._b.bTime, 112) + CONVERT(nvarchar, dbo._a.aId))
IN
(SELECT CONVERT(nvarchar, t, 112) + CONVERT(nvarchar, aId)
AS Expr1
FROM (SELECT MAX(bTime) AS t, aId
FROM dbo._b AS _b_1
GROUP BY aId) AS derivedtbl_1))
ORDER BY dbo._b.aId

t-sql解法(2)

WITH tt AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY aId ORDER BY dbo._b.bTime) AS rn,
dbo._b.aId, dbo._b.bId, dbo._a.aName, dbo._b.bName, dbo._b.bTime
FROM dbo.Orders
)
SELECT dbo._b.aId, dbo._b.bId, dbo._a.aName, dbo._b.bName, dbo._b.bTime
FROM tt
WHERE rn = 1

linq解法(目前想到的解法..感覺不夠好..一﹏一||..)

List<int> li = new List<int>();
var ienumGroup = db._b.GroupBy(source => source.aId);
foreach (var group in ienumGroup)
{
int bId = group.OrderByDescending(source => source.bTime)
.Select(source => source.bId).Take(1).Single();
li.Add(bId);
}
var resoult = db._b.Where(source => li.Contains(source.bId))
.OrderBy(source => source.aId);

//print結果
foreach (var a in resoult)
{
Response.Write(a.aId + "," + a.bId + "," + a._a.aName
+ "," + a.bName + "," + a.bTime + "<br />");
}

結果

dfasdfdfasd

沒有留言: