--创建合并处理函数 create function f_str(@name varchar(20),@ClassName varchar(20)) returns varchar(8000) as begin declare @re varchar(8000) set @re='' select @re=@re+','+RecordID+cast(count(*) as varchar) from StdInof where name=@name and ClassName=@ClassName group by RecordID return(substring(@re,2,8000)) end go--调用实现查询 select Name,ClassName,處罰記錄=dbo.f_str(Name,ClassName) from StdInof group by Name,ClassName
--测试--测试数据 create table StdInof(ID int,Name varchar(20),ClassName varchar(20),RecordID varchar(20)) insert StdInof select 10,'小明','一班','記大過' union all select 11,'小明','一班','警告' union all select 12,'小張','三班','記大過' union all select 13,'小明','一班','警告' union all select 14,'小張','三班','記過' union all select 15,'小明','一班','留校查看' union all select 16,'小張','三班','記過' go--创建合并处理函数 create function f_str(@name varchar(20),@ClassName varchar(20)) returns varchar(8000) as begin declare @re varchar(8000) set @re='' select @re=@re+','+RecordID+cast(count(*) as varchar) from StdInof where name=@name and ClassName=@ClassName group by RecordID return(substring(@re,2,8000)) end go--调用实现查询 select Name,ClassName,處罰記錄=dbo.f_str(Name,ClassName) from StdInof group by Name,ClassName go--删除测试 drop table StdInof drop function f_str/*--测试结果 Name ClassName 處罰記錄 -------------------- -------------------- -------------------------- 小明 一班 記大過1,警告2,留校查看1 小張 三班 記大過1,記過2(所影响的行数为 2 行)--*/
樓主自己修改一下--函數,合並字符串欄位 --測試數據 /* CREATE table person_info(dept_name varchar(10),position_name varchar(10),person_name varchar(10)) INSERT INTO person_info SELECT '資訊部','軟件編碼員','小李' UNION ALL SELECT '資訊部','軟件編碼員','小王' UNION ALL SELECT '行政部','秘書','小芳' */ --合並函數 CREATE FUNCTION FunMergeCharField(@vchA varchar(10),@vchB varchar(10)) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r='' SELECT @r=@r+','+person_name FROM person_info WHERE dept_name=@vchA and position_name=@vchB RETURN(substring(@r,2,8000)) END GO --删除测试 DROP TABLE person_info DROP FUNCTION FunMergeCharField--調用 SELECT dept_name,position_name,在職人員=dbo.FunMergeCharField(dept_name,position_name) FROM person_info GROUP BY dept_name,position_name go
--如果要生成编号,将调用改为:select 編號=(select count(distinct Name) from StdInof where Name<=a.Name) ,姓名=Name,班級=ClassName,處罰記錄=dbo.f_str(Name,ClassName) from StdInof a group by Name,ClassName
--测试--测试数据 create table StdInof(ID int,Name varchar(20),ClassName varchar(20),RecordID varchar(20)) insert StdInof select 10,'小明','一班','記大過' union all select 11,'小明','一班','警告' union all select 12,'小張','三班','記大過' union all select 13,'小明','一班','警告' union all select 14,'小張','三班','記過' union all select 15,'小明','一班','留校查看' union all select 16,'小張','三班','記過' go--创建合并处理函数 create function f_str(@name varchar(20),@ClassName varchar(20)) returns varchar(8000) as begin declare @re varchar(8000) set @re='' select @re=@re+','+RecordID+cast(count(*) as varchar) from StdInof where name=@name and ClassName=@ClassName group by RecordID return(substring(@re,2,8000)) end go--调用实现查询 select 編號=(select count(distinct Name) from StdInof where Name<=a.Name) ,姓名=Name,班級=ClassName,處罰記錄=dbo.f_str(Name,ClassName) from StdInof a group by Name,ClassName go--删除测试 drop table StdInof drop function f_str/*--测试结果編號 姓名 班級 處罰記錄 --- ----------- ------------ ------------------------ 1 小明 一班 記大過1,警告2,留校查看1 2 小張 三班 記大過1,記過2(所影响的行数为 2 行)--*/
create function f_str(@name varchar(20),@ClassName varchar(20))
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+RecordID+cast(count(*) as varchar)
from StdInof where name=@name and ClassName=@ClassName
group by RecordID
return(substring(@re,2,8000))
end
go--调用实现查询
select Name,ClassName,處罰記錄=dbo.f_str(Name,ClassName)
from StdInof
group by Name,ClassName
create table StdInof(ID int,Name varchar(20),ClassName varchar(20),RecordID varchar(20))
insert StdInof select 10,'小明','一班','記大過'
union all select 11,'小明','一班','警告'
union all select 12,'小張','三班','記大過'
union all select 13,'小明','一班','警告'
union all select 14,'小張','三班','記過'
union all select 15,'小明','一班','留校查看'
union all select 16,'小張','三班','記過'
go--创建合并处理函数
create function f_str(@name varchar(20),@ClassName varchar(20))
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+RecordID+cast(count(*) as varchar)
from StdInof where name=@name and ClassName=@ClassName
group by RecordID
return(substring(@re,2,8000))
end
go--调用实现查询
select Name,ClassName,處罰記錄=dbo.f_str(Name,ClassName)
from StdInof
group by Name,ClassName
go--删除测试
drop table StdInof
drop function f_str/*--测试结果
Name ClassName 處罰記錄
-------------------- -------------------- --------------------------
小明 一班 記大過1,警告2,留校查看1
小張 三班 記大過1,記過2(所影响的行数为 2 行)--*/
--測試數據
/*
CREATE table person_info(dept_name varchar(10),position_name varchar(10),person_name varchar(10))
INSERT INTO person_info
SELECT '資訊部','軟件編碼員','小李'
UNION ALL SELECT '資訊部','軟件編碼員','小王'
UNION ALL SELECT '行政部','秘書','小芳'
*/
--合並函數
CREATE FUNCTION FunMergeCharField(@vchA varchar(10),@vchB varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+person_name FROM person_info WHERE dept_name=@vchA and position_name=@vchB
RETURN(substring(@r,2,8000))
END
GO
--删除测试
DROP TABLE person_info
DROP FUNCTION FunMergeCharField--調用
SELECT dept_name,position_name,在職人員=dbo.FunMergeCharField(dept_name,position_name)
FROM person_info
GROUP BY dept_name,position_name
go
,姓名=Name,班級=ClassName,處罰記錄=dbo.f_str(Name,ClassName)
from StdInof a
group by Name,ClassName
create table StdInof(ID int,Name varchar(20),ClassName varchar(20),RecordID varchar(20))
insert StdInof select 10,'小明','一班','記大過'
union all select 11,'小明','一班','警告'
union all select 12,'小張','三班','記大過'
union all select 13,'小明','一班','警告'
union all select 14,'小張','三班','記過'
union all select 15,'小明','一班','留校查看'
union all select 16,'小張','三班','記過'
go--创建合并处理函数
create function f_str(@name varchar(20),@ClassName varchar(20))
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+RecordID+cast(count(*) as varchar)
from StdInof where name=@name and ClassName=@ClassName
group by RecordID
return(substring(@re,2,8000))
end
go--调用实现查询
select 編號=(select count(distinct Name) from StdInof where Name<=a.Name)
,姓名=Name,班級=ClassName,處罰記錄=dbo.f_str(Name,ClassName)
from StdInof a
group by Name,ClassName
go--删除测试
drop table StdInof
drop function f_str/*--测试结果編號 姓名 班級 處罰記錄
--- ----------- ------------ ------------------------
1 小明 一班 記大過1,警告2,留校查看1
2 小張 三班 記大過1,記過2(所影响的行数为 2 行)--*/