--1create table T(id int,name varchar(20),rank int)
insert T select 1, 'I', 1
union all select 1, 'have', 2
union all select 1, 'a', 3
union all select 1, 'dream', 4
union all select 2, 'Oh', 1
union all select 2, 'my', 2
union all select 2, 'god!', 3create function fun(@id int)
returns varchar(4000)
as
begin
declare @re varchar(4000)
set @re=''
select @re=@re+name+' ' from T where id=@id order by rank return @re
endselect id, name=dbo.fun(id) from T group by id--result
id name
----------- ---------------------
1 I have a dream
2 Oh my god! (2 row(s) affected)
insert T select 1, 'I', 1
union all select 1, 'have', 2
union all select 1, 'a', 3
union all select 1, 'dream', 4
union all select 2, 'Oh', 1
union all select 2, 'my', 2
union all select 2, 'god!', 3create function fun(@id int)
returns varchar(4000)
as
begin
declare @re varchar(4000)
set @re=''
select @re=@re+name+' ' from T where id=@id order by rank return @re
endselect id, name=dbo.fun(id) from T group by id--result
id name
----------- ---------------------
1 I have a dream
2 Oh my god! (2 row(s) affected)
create function f_str(@id int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+name from table1 where id = @id
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select id,name=dbo.f_str(id) from table1 group by id order by 1
go
from tableA A
where not exists
( select 1 from tableB where a.name=A.name and ........)
where not exists
(select * from B where BeginDate=A.BeginDate and EndDate=A.EndDate and Name=A.Name and StrNum=A.StrNum and Number=A.Number)