求一SQL，符合条件的数据用一列显示

[KKL]

--如查询出分数大于50的科目，用一行显示，中间用分号显示
CREATE TABLE TAB
(
	stu_score float,
	stu_subject varchar(10)
)
INSERT INTO TAB
SELECT 80,'语文' UNION 
SELECT 60,'数学' UNION 
SELECT 50,'英语'

[百年树人]

select 
  stuff((select ','+stu_subject from tab where stu_score>=60 for xml path('')),1,1,'')

[快溜]

这是什么贴？

[百年树人]

[Quote=引用 2 楼 josy 的回复:]
SQL code
select
stuff((select ','+stu_subject from tab where stu_score>=60 for xml path('')),1,1,'')
[/Quote]
是大于50

select 
  stuff((select ','+stu_subject from tab where stu_score>50 for xml path('')),1,1,'')
/**

------------------------------------------------------
数学,语文

(1 行受影响)
**/

[yibey]

楼主干吗，来提问然后自己说出答案再然后给分？

[快溜]

CREATE TABLE TAB
(
    stu_score float,
    stu_subject varchar(10)
)
INSERT INTO TAB
SELECT 80,'语文' UNION 
SELECT 60,'数学' UNION 
SELECT 50,'英语'

SELECT stuff((SELECT ';'+stu_subject FROM TAB WHERE stu_score>50 FOR XML PATH('')),1,1,'')

/*

---------
数学;语文

[AcHerat]

select 
  stuff((select ','+stu_subject from tab where stu_score >= 50 for xml path('')),1,1,'') as p

--如果还要其他的数据用 union all 再去 select  注意where的条件。

[水族杰纶]

select stuff((select ','+stu_subject 
from tab 
where stu_score>50
for xml path('')),1,1,'')
drop table tab

[rucypli]

楼上和楼主是同一个人？

[dearbinge]

SELECT (SELECT stu_subject+';' FROM TAB WHERE stu_score>50 FOR XML PATH('')) AS p

楼主来展示他的头像了！

[yibey]

[Quote=引用 6 楼 yibey 的回复:]

楼主干吗，来提问然后自己说出答案再然后给分？
[/Quote]

o my god 我吧1楼和楼主看成一个人了罪过

[水族杰纶]

CREATE TABLE TAB
(
    stu_score float,
    stu_subject nvarchar(10)
)
INSERT INTO TAB
SELECT 80,N'语文' UNION 
SELECT 60,N'数学' UNION 
SELECT 50,N'英语'
declare @s nvarchar(500)
select @s=isnull(@s+',','')+stu_subject
from tab
where stu_score>50
select @s
drop table tab