这条语句实在写不出了，

这条语句实在写不出了，请教高手！
表一
CarID  
1  
2  

表二

id perCarID scort
1 1 98
2 2 50
3 2 68
4 1 70
5 2 80
6 1 92

表一 id与表二perID关联的。

我想得到的结果是将它们分组后把分数靠近最高的前二条记录连接起来。即是


1 98,92
2 80,68

[解决办法]

SQL code

DECLARE @t1 TABLE(CarID INT)DECLARE @t2 TABLE(id INT,perCarID INT,scort INT)INSERT INTO @t1SELECT 1 UNION ALLSELECT 2INSERT INTO @t2SELECT '1','1','98' UNION ALLSELECT '2','2','50' UNION ALLSELECT '3','2','68' UNION ALLSELECT '4','1','70' UNION ALLSELECT '5','2','80' UNION ALLSELECT '6','1','92';WITH CTE AS(SELECT * FROM @t1 t1     CROSS APPLY     (        SELECT TOP 2 scort          FROM @t2         WHERE t1.CarID=perCarID        ORDER BY scort DESC    ) t2)SELECT CarID,scort=STUFF((SELECT ','+CAST(scort AS VARCHAR) FROM CTE WHERE CarID=c.CarID FOR XML PATH('') ),1,1,'' ) FROM CTE cGROUP BY c.CarID