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
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
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,美术:不及格
*/
@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
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
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 '李四','美术','中'
gocreate 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
goselect 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='不及格'
)axm bujige
---------- ----------------------
李四 语文:32
张三 数学:54,美术:不及格
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