这条语句实在写不出了，请教高手！

[xiaoju03]

表一
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

[迪迦凹凸曼]

先按scort  desc 取top2 然后在用分隔符连起来

DECLARE @t1 TABLE(CarID INT)
DECLARE @t2 TABLE(id INT,perCarID INT,scort INT)
INSERT INTO @t1
        ( CarID )
VALUES  (1 ),(2)
INSERT INTO @t2
        ( id, perCarID, scort )
VALUES  ( 1,1,98),( 2,2,50),( 3,2,68),( 4,1,70),( 5,2,80),( 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
/*
CarID       scort
----------- -----------------
1           98,92
2           80,68

(2 行受影响)
*/

[以学习为目的]

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

[wu5224311]

顶2楼····

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

[Quote=引用 2 楼 的回复:]

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' U……
[/Quote]

[WilliamMoore]

LZ的数据库是那个版本 如果是2005以上可以用上面的方法

如果是sql server2000的话就不行了 2005之后才加的 FOR XML PATH

sql server2000只能用辅助函数来处理

create  table t1(CarID int);
create  table t2(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'

create function fun(@perCarID int)  
	returns varchar(8000)  
as  
begin    
	declare @str varchar(8000)    
	set @str = ''    
	select @str = @str + ',' + cast(scort as varchar) from t2 
		where perCarID = @perCarID;    
	set @str = right(@str , len(@str) - 1)    
	return(@str)  
End  

select perCarID,dbo.fun(perCarID) from 
	t1,t2 where t1.CarID = t2.perCarID group by perCarID

[xiaoju03]

lordbaby大哥强，以后多多向你学习。先接分！