合并重复数据的问题。我有二张表，一张是公司名称表:CompanyName，还有一张是公司人才招聘表:JobCompanyName
合并重复数据的问题。
我有二张表，一张是公司名称表: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 code
Create 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 行受影响)*/
[解决办法]
- SQL code
------ 创建测试数据create table CompanyName(CompanyID int, CmpanyName varchar(20), createdate smalldatetime)insert into CompanyNameselect 1,'园裕电子','2000-08-01' unionselect 2,'竞陆电子','2001-04-18'Create table job(JobID int , Re_CompanyID int, JobPost varchar(20), createdate smalldatetime)insert into jobselect 1,1,'会计',getdate() unionselect 2,1,'人事',getdate()-1 unionselect 3,1,'资讯',getdate()-1 unionselect 4,2,'财务',getdate() unionselect 5,2,'MIS',getdate()-2 unionselect 6,2,'业务',getdate() -----创建合并字符串函数Create function dbo.f_str2(@id int,@rq varchar(10)) returns varchar(100)asbegin declare @str varchar(1000) set @str = '' select @str = @str + ',' + cast(jobpost as varchar) from job where Re_CompanyID= @id and convert(varchar(10),createdate,120)=@rq set @str = right(@str , len(@str) - 1) return @strend调用函数select distincta.CmpanyName,dbo.f_str2(a.CompanyID,convert(varchar(10),b.createdate,120)) as job ,convert(varchar(10),b.createdate,120) as rqfrom CompanyName a,job bwhere a.CompanyID=b.Re_CompanyIDorder by convert(varchar(10),b.createdate,120)-----------------------------------竞陆电子MIS 2009-05-29 园裕电子人事,资讯 2009-05-30 竞陆电子财务,业务 2009-05-31 园裕电子会计 2009-05-31
[解决办法]
- SQL code
create table #CompanyName(CompanyID int, CmpanyName varchar(20), createdate smalldatetime)insert into #CompanyNameselect 1,'园裕电子','2000-08-01' unionselect 2,'竞陆电子','2001-04-18'Create table job(JobID int , Re_CompanyID int, JobPost varchar(20), createdate smalldatetime)insert into jobselect 1,1,'会计',getdate() unionselect 2,1,'人事',getdate()-1 unionselect 3,1,'资讯',getdate()-1 unionselect 4,2,'财务',getdate() unionselect 5,2,'MIS',getdate()-2 unionselect 6,2,'业务',getdate() select * from jobcreate function dbo.f_strJob2(@id int) returns varchar(1000)begin declare @str varchar(1000) set @str='' select @str=@str+','+JobPost from job where Re_CompanyID = @id return stuff(@str,1,1,'')end select C.CmpanyName,j.* from #CompanyName C join( select distinct(dbo.f_strJob2(j.Re_CompanyID)) JobPost,C.createdate,j.Re_CompanyID from job j join (select Re_CompanyID,max(createdate) createdate from job group by Re_CompanyID) con j.Re_CompanyID=c.Re_CompanyID) j on j.Re_CompanyID=C.CompanyIDCmpanyName JobPost createdate Re_CompanyID-------------------- ---------------------------------------------------------------------------------------------------------------- ----------------------- ------------竞陆电子 财务,MIS,业务 2009-05-31 13:42:00 2园裕电子 会计,人事,资讯 2009-05-31 13:42:00 1(2 行受影响)
[解决办法]
学习学习！！
[解决办法]
经典
------解决方案--------------------
有点意思。
[解决办法]
ding ~~~~~~~~~~~~~~~~~~~``
[解决办法]
ding ~~~~~~~~
[解决办法]
呵呵，路过～～～
[解决办法]
mark
[解决办法]
支持一下
[解决办法]
学习……
[解决办法]
mark...