这条语句实在写不出了，

这条语句实在写不出了，请教高手！
表一
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 @t1
SELECT 1 UNION ALL
SELECT 2
INSERT INTO @t2
SELECT '1','1','98' UNION ALL
SELECT '2','2','50' UNION ALL
SELECT '3','2','68' UNION ALL
SELECT '4','1','70' UNION ALL
SELECT '5','2','80' UNION ALL
SELECT '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 c
GROUP BY c.CarID

这条语句实在写不出了，

相关推荐