• 主页
  • 基础类
  • 应用实例
  • 新技术前沿

这条语句实在写不出了,请教高手!

xiaoju03 2012-08-29 06:02:00
表一
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
...全文
128 点赞 收藏 5
写回复
5 条回复
xiaoju03 2012年08月30日
lordbaby大哥强,以后多多向你学习。先接分!
回复 点赞
Rola 2012年08月30日
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

回复 点赞
wu5224311 2012年08月30日
顶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]
回复 点赞
以学习为目的 2012年08月29日
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
回复 点赞
迪迦凹凸曼 2012年08月29日

先按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 行受影响)
*/

回复 点赞
发动态
发帖子
MS-SQL Server
创建于2007-09-28

1.4w+

社区成员

25.3w+

社区内容

MS-SQL Server相关内容讨论专区
社区公告
暂无公告