create function f_getstr(@flag int,@fstoreid varchar(10)) returns varchar(200) as BEGIN DECLARE @r VARCHAR(200) SET @r = '' IF @flag = 1 BEGIN SELECT @r = @r + ',' + FCHECKERID FROM TBCHECK A, TBMAN B WHERE A.FCHECKERID = B.FPERSONID AND FSTOREID = @fstoreid END IF @flag = 2 BEGIN SELECT @r = @r + ',' + FPERSONNAME FROM TBCHECK A, TBMAN B WHERE A.FCHECKERID = B.FPERSONID AND FSTOREID = @fstoreid END RETURN (STUFF(@r,1,1,'')) ENDSELECT freqmanid=B.freqmanid, STOREID = A.FSTOREID, FCHECKERID = DBO.F_GETSTR(1,A.FSTOREID), FPERSONNAME = DBO.F_GETSTR(2,A.FSTOREID) FROM TBCHECK A,tbreq B WHERE A.fstoreid=B.storid GROUP BY B.freqmanid,A.FSTOREID
create table tb(fid int,freqmanid varchar(10),storid varchar(10)) insert into tb select 1,'a1','a' union all select 2,'a2','b' union all select 3,'a3','c' gocreate table tt(fcheckerid varchar(10),fstoreid varchar(10)) insert into tt select '001','a' union all select '002','a' union all select '003','b' union all select '004','c' union all select '005','b' gocreate table ta(fpersonid varchar(10),fpersonname varchar(10)) insert into ta select '001','王三' union all select '002','李四' union all select '003','王五' union all select '004','张七' union all select '005','里八' gocreate function dbo.fc_str1(@fstoreid varchar(100)) returns varchar(100) as begin declare @sql varchar(1000) set @sql='' select @sql=@sql+','+cast(fcheckerid as varchar(100)) from tt where fstoreid=@fstoreid return stuff(@sql,1,1,'') end gocreate function dbo.fc_str2(@fstoreid varchar(100)) returns varchar(100) as begin declare @sql varchar(1000) set @sql='' select @sql=@sql+','+cast(fpersonname as varchar(100)) from ta a,tt b where a.fpersonid=b.fcheckerid and fstoreid=@fstoreid return stuff(@sql,1,1,'') end goselect a.freqmanid,a.storid,dbo.fc_str1(b.fstoreid) as fcheckerid,dbo.fc_str2(b.fstoreid) as fpersonname from tb a,tt b where a.storid=b.fstoreid group by a.freqmanid,a.storid,b.fstoreiddrop table tb,tt,ta drop function dbo.fc_str1,dbo.fc_str2
returns varchar(200)
as
BEGIN
DECLARE
@r VARCHAR(200)
SET @r = ''
IF @flag = 1
BEGIN
SELECT @r = @r + ',' + FCHECKERID
FROM TBCHECK A,
TBMAN B
WHERE A.FCHECKERID = B.FPERSONID
AND FSTOREID = @fstoreid
END
IF @flag = 2
BEGIN
SELECT @r = @r + ',' + FPERSONNAME
FROM TBCHECK A,
TBMAN B
WHERE A.FCHECKERID = B.FPERSONID
AND FSTOREID = @fstoreid
END
RETURN (STUFF(@r,1,1,''))
ENDSELECT freqmanid=B.freqmanid,
STOREID = A.FSTOREID,
FCHECKERID = DBO.F_GETSTR(1,A.FSTOREID),
FPERSONNAME = DBO.F_GETSTR(2,A.FSTOREID)
FROM TBCHECK A,tbreq B
WHERE A.fstoreid=B.storid
GROUP BY B.freqmanid,A.FSTOREID
insert into tb select 1,'a1','a'
union all select 2,'a2','b'
union all select 3,'a3','c'
gocreate table tt(fcheckerid varchar(10),fstoreid varchar(10))
insert into tt select '001','a'
union all select '002','a'
union all select '003','b'
union all select '004','c'
union all select '005','b'
gocreate table ta(fpersonid varchar(10),fpersonname varchar(10))
insert into ta select '001','王三'
union all select '002','李四'
union all select '003','王五'
union all select '004','张七'
union all select '005','里八'
gocreate function dbo.fc_str1(@fstoreid varchar(100))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast(fcheckerid as varchar(100)) from tt where fstoreid=@fstoreid
return stuff(@sql,1,1,'')
end
gocreate function dbo.fc_str2(@fstoreid varchar(100))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast(fpersonname as varchar(100)) from ta a,tt b where a.fpersonid=b.fcheckerid and fstoreid=@fstoreid
return stuff(@sql,1,1,'')
end
goselect a.freqmanid,a.storid,dbo.fc_str1(b.fstoreid) as fcheckerid,dbo.fc_str2(b.fstoreid) as fpersonname from tb a,tt b where a.storid=b.fstoreid group by a.freqmanid,a.storid,b.fstoreiddrop table tb,tt,ta
drop function dbo.fc_str1,dbo.fc_str2