判断分数的问题，同时有数字和中文。。。

[blw]

有表如下
xm kc fenshu
张三 语文 77
张三 数学 54
张三 英语 63
张三 美术 不及格
李四 语文 32
李四 数学 78
李四 英语 92
李四 美术 中


现在想查出这样的结果
xm bujige
张三 数学:54,美术:不及格
李四 语文:32

[shanxian]

行列转换的题目.

[paoluo]

Create Function F_TEST(@xm Nvarchar(100))
Returns Nvarchar(1000)
As
Begin
Declare @S Nvarchar(1000)
Select @S = ''
Select @S = @S + ',' + kc + ':' + fenshu From TEST Where (fenshu = N'不及格' Or (IsNumeric(fenshu) = 1 And fenshu <= 60)) And xm = @xm
Select @S = Stuff(@S, 1, 1, '')
Return @S
End
GO
Select
xm,
dbo. F_TEST(xm) As bujige
From
TEST
Group By
xm
GO

[paoluo]

--創建測試環境
Create Table TEST
(xm Nvarchar(100),
kc Nvarchar(100),
fenshu Nvarchar(100))
Insert TEST Select N'张三', N'语文', N'77'
Union All Select N'张三', N'数学', N'54'
Union All Select N'张三', N'英语', N'63'
Union All Select N'张三', N'美术', N'不及格'
Union All Select N'李四', N'语文', N'32'
Union All Select N'李四', N'数学', N'78'
Union All Select N'李四', N'英语', N'92'
Union All Select N'李四', N'美术', N'中'
GO
--創建函數
Create Function F_TEST(@xm Nvarchar(100))
Returns Nvarchar(1000)
As
Begin
Declare @S Nvarchar(1000)
Select @S = ''
Select @S = @S + ',' + kc + ':' + fenshu From TEST Where (fenshu = N'不及格' Or (IsNumeric(fenshu) = 1 And fenshu <= 60)) And xm = @xm
Select @S = Stuff(@S, 1, 1, '')
Return @S
End
GO
--測試
Select
xm,
dbo. F_TEST(xm) As bujige
From
TEST
Group By
xm
GO
--刪除測試環境
Drop Table TEST
Drop Function F_TEST
--結果
/*
xm bujige
李四 语文:32
张三 数学:54,美术:不及格
*/

[昵称被占用了]

写个函数

create function fn_不及格(
@xm varchar(30)
)
returns varchar(80)
as
begin
declare @r varchar(80)
set @r=''
select @r=@r+','+kc+':'+fenshu from 表 where xm=@xm
and (fenshu='不及格' or case when ISNUMERIC(fenshu) then cast(fenshu as numeric(10,2) else 100 end<60)
set @r=stuff(@r,1,1,'')
return @r
end
go

--查询

select xm,dbo.fn_不及格(xm) as bujige
from 表
where fenshu='不及格' or case when ISNUMERIC(fenshu) then cast(fenshu as numeric(10,2) else 100 end<60
group by xm

[昵称被占用了]

ISNUMERIC(fenshu)==>ISNUMERIC(fenshu)=1

[chuifengde]

create function fun_xyz(@xm varchar(10))
returns varchar(1000)
as
begin
declare @y varchar(1000)
select @y=isnull(@y+',','') + kc+':'+fenshu from [Table] where xm=@xm and (isnumeric(fenshu)=1 and right('00'+fenshu,2)<='60') or fenshu='不及格'
return @y
end
go
select xm,dbo.fun_xyz(xm) from [Table] group by xm

[bill024]

create table test(xm varchar(10),kc varchar(10),fenshu varchar(15))
insert test select '张三','语文','77'
union all select '张三','数学','54'
union all select '张三','英语','63'
union all select '张三','美术','不及格'
union all select '李四','语文','32'
union all select '李四','数学','78'
union all select '李四','英语','92'
union all select '李四','美术','中'
go

create function dbo.fn_Merge(@xm varchar(1000))
returns varchar(1000)
as
begin
declare @name varchar(8000)
set @name=''
select @name=@name+','+kc+':'+fenshu from
(
select xm,kc,fenshu from test where isnumeric(fenshu)=1 and cast(fenshu as int)<60
union all
select xm,kc,fenshu from test where isnumeric(fenshu)=0 and fenshu='不及格'
)a
where xm=@xm
return stuff(@name,1,1,'')
end
go

select distinct xm,bujige=dbo.fn_Merge(xm) from
(
select xm,kc,fenshu from test where isnumeric(fenshu)=1 and cast(fenshu as int)<60
union all
select xm,kc,fenshu from test where isnumeric(fenshu)=0 and fenshu='不及格'
)a

xm bujige
---------- ----------------------
李四 语文:32
张三 数学:54,美术:不及格

[chuifengde]

==>right('00'+fenshu,3)<'060'

[yrwx001]

create function fn_test(@name varchar(20))
returns varchar(200)
as
begin
declare @str varchar(200)
declare @sql varchar(8000)
set @sql = ''
set @str = ''
select @str = @str + ';'+ kc + ':' + fenshu from #t where xm = @name and (fenshu <'60' or fenshu = '不及格')
set @str = stuff(@str,1,1,'')
return @str
end


select xm, bujige = dbo.fn_test(xm) from #t