34,577
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('bsn_company_info') IS NOT NULL DROP TABLE bsn_company_info
IF OBJECT_ID('bsn_company_job') IS NOT NULL DROP TABLE bsn_company_job
CREATE TABLE bsn_company_info(id INT,m_id INT,[name] NVARCHAR(20),tel VARCHAR(20))
CREATE TABLE bsn_company_job(id INT,m_id INT,[job_name] NVARCHAR(20),add_time DATETIME)
SET NOCOUNT ON
INSERT INTO bsn_company_info VALUES (1,2,'小王有限公司','18658784412')
INSERT INTO bsn_company_info VALUES (2,3,'小张有限公司','15878954215')
INSERT INTO bsn_company_job VALUES (1,2,'装配工','2017-11-20')
INSERT INTO bsn_company_job VALUES (2,2,'仓管员','2017-11-21')
INSERT INTO bsn_company_job VALUES (3,3,'厨师','2017-10-25')
INSERT INTO bsn_company_job VALUES (4,3,'帮工','2017-11-01')
SELECT a.m_id
,[name]
,STUFF(
(
SELECT ','+b.job_name FROM bsn_company_job AS b WHERE a.m_id=b.m_id
ORDER BY b.add_time DESC --这个是后添加的在前面,你希望反过来改成ASC就好
FOR XML PATH('')
),1,1,''
) AS jobName
FROM bsn_company_info AS a
/*
m_id name jobName
----------- -------------------- ---------------------
2 小王有限公司 仓管员,装配工
3 小张有限公司 帮工,厨师
*/
--你的结果数据和你描述的需求不一致,但也就只是个排序问题,有问题改排序就是了
if OBJECT_ID(N'tempdb..#bsn_company_info') is not null
drop table #bsn_company_info
go
create table #bsn_company_info
(id int identity(1,1),
mid varchar(10),
name varchar(100),
tel varchar(20))
insert into #bsn_company_info
select '2','小王有限公司','18658784412' union all
select '3','小张有限公司','15878954215'
go
if OBJECT_ID(N'tempdb..#bsn_company_job') is not null
drop table #bsn_company_job
go
create table #bsn_company_job
(id int identity(1,1),
mid varchar(10),
job_name nvarchar(20),
add_time datetime)
insert into #bsn_company_job
select '2','装配工','2017-11-20' union all
select '2','仓管员','2017-11-21' union all
select '3','厨师','2017-10-25' union all
select '3','帮工','2017-11-01'
go
select A.mid,A.name,B.job_name
from #bsn_company_info A
join
(select mid,MAX(add_time) as add_time,
stuff((select ','+job_name from #bsn_company_job where mid=A.mid for XML path ('')),1,1,'') as job_name
from #bsn_company_job A
group by mid) as B ON A.mid=B.mid
order by B.add_time desc