with tb as( select X.a,X.b,isnull(Y.c,X.c) as c from score X left join (select top 1 * from score where b=10 )Y on X.b=Y.b) select a=STUFF((select ','+cast(a as varchar(10)) from tb F where f.b=g.b and f.c =g.c for xml path('')),1,1,'') ,b,c from tb G group by B,c
我的是SQL2000 可以用上面哪条语句?
第一步: 求出来的表格 select a, b, c = case when b = 10 then 0 else c end from score
select DISTINCT b.A,a.B,CASE WHEN a.B=10 THEN 0 ELSE a.C END from score AS a CROSS APPLY(SELECT STUFF((SELECT ','+A FROM score WHERE B=a.B AND (C=a.C OR B=10) ORDER BY A FOR XML PATH('')),1,1,'') AS A) AS b
select X.a,X.b,isnull(Y.c,X.c) as c from score X left join (select top 1 * from score where b=10 )Y
on X.b=Y.b)
select a=STUFF((select ','+cast(a as varchar(10)) from tb F where f.b=g.b and f.c =g.c for xml path('')),1,1,'')
,b,c from tb G group by B,c
求出来的表格
select a, b, c = case when b = 10 then 0 else c end from score
select DISTINCT b.A,a.B,CASE WHEN a.B=10 THEN 0 ELSE a.C END from score AS a
CROSS APPLY(SELECT STUFF((SELECT ','+A FROM score WHERE B=a.B AND (C=a.C OR B=10) ORDER BY A FOR XML PATH('')),1,1,'') AS A) AS b
第 2 行: 'APPLY' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 2
在关键字 'FOR' 附近有语法错误。提示上面的错误 ben