合并重复数据的有关问题_Sql Server

合并重复数据的问题。
我有二张表，一张是公司名称表:CompanyName，还有一张是公司人才招聘表:Job
CompanyName表中有字段：CompanyID,CompanyName,date
Job表中有字段：JobID,Re_CompanyID,JobPost,date

现在我想Job表中相同公司的职位合并在一个列并取出，排序按Job表中的日期来。

------解决方案--------------------

SQL code

--1、sql2000中只能用自定义的函数解决create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')gocreate function dbo.f_str(@id int) returns varchar(100)asbegin    declare @str varchar(1000)    set @str = ''    select @str = @str + ',' + cast(value as varchar) from tb where id = @id    set @str = right(@str , len(@str) - 1)    return @strendgo--调用函数select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_strdrop table tb--2、sql2005中的方法create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')from tbgroup by iddrop table tb
------解决方案--------------------
SQL codeCreate table CompanyName (CompanyID int,CompanyName varchar(10),date datetime)insert CompanyName values(1,'公司1',getdate())insert CompanyName values(2,'公司2',getdate())insert CompanyName values(3,'公司3',getdate())Create table Job (JobID int,Re_CompanyID int,JobPost varchar(20),date datetime)insert Job values(1,1,'SQL Server DBA',getdate())insert Job values(2,1,'Delphi',getdate())insert Job values(3,1,'Oracle DBA',getdate())insert Job values(4,2,'ASP.Net',getdate())insert Job values(5,3,'C#',getdate())GO--样式一select a.*,b.JobPost from CompanyName a join Job b on a.companyid=b.Re_CompanyID order by a.CompanyName,b.date/*CompanyID   CompanyName date                    JobPost----------- ----------- ----------------------- --------------------1           公司1         2009-05-31 11:03:20.793 SQL Server DBA1           公司1         2009-05-31 11:03:20.793 Delphi1           公司1         2009-05-31 11:03:20.793 Oracle DBA2           公司2         2009-05-31 11:03:20.793 ASP.Net3           公司3         2009-05-31 11:03:20.793 C#*/--样式二GO--创建一个函数用于合并字符串create function dbo.f_str    (@id int)     returns varchar(1000)begin    declare @str varchar(1000)    select @str = isnull(@str + ',','') +JobPost from job where Re_CompanyID = @id order by date    return @strendgoselect *,JobPost=dbo.f_str(CompanyID) from CompanyName order by CompanyName/*CompanyID   CompanyName date                    JobPost----------- ----------- ----------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1           公司1         2009-05-31 11:05:33.560 SQL Server DBA,Delphi,Oracle DBA2           公司2         2009-05-31 11:05:33.577 ASP.Net3           公司3         2009-05-31 11:05:33.577 C#(3 行受影响)*/