求教一个sql的写法

[woshizhaoxuhui]

有如下三个表
1，用户表
user_id user_name
1 小王
2 小张
3 小李
4 小周

2，岗位表
job_id job_name
1 前台
2 后厨

3，岗位明细表
job_id user_id
1 1
1 2
2 3
2 4

现在想得到如下结果
岗位编号 岗位名称 可用人员
1 前台 小王，小张
2 后厨 小李，小周

请问该如何来写sql？请大家指点一下

[水族杰纶]

;with tmp as (select a.job_id,c.job_name,b.user_name from 岗位明细表 a inner join 用户表 b on a.user_id=b.user_id inner join 岗位表 c on a.job_id=c.job_id ) select job_id, job_name, stuff((select ','+user_name from tmp where job_id=t.job_id for xml path('')),1,1,'') from tmp t

[lzw_0736]

WITH a (user_id,USER_NAME) AS
(
SELECT 1,'小王' UNION ALL
SELECT 2,'小张' UNION ALL
SELECT 3,'小李' UNION ALL
SELECT 4,'小周'
)
,b (job_id,job_name) AS 
(
SELECT 1,'前台' UNION ALL
SELECT 2,'后厨'
)
,c (job_id,user_id) AS
(
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 2,4
)
SELECT *,USER_NAME=
STUFF((SELECT '，'+a.USER_NAME FROM c JOIN a ON c.user_id=a.user_id 
WHERE c.job_id=b.job_id FOR XML PATH('')),1,1,'')
FROM b

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-13 10:33:32
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[用户表]
if object_id('[用户表]') is not null drop table [用户表]
go 
create table [用户表]([user_id] int,[user_name] varchar(4))
insert [用户表]
select 1,'小王' union all
select 2,'小张' union all
select 3,'小李' union all
select 4,'小周'
--> 测试数据：[岗位表]
if object_id('[岗位表]') is not null drop table [岗位表]
go 
create table [岗位表]([job_id] int,[job_name] varchar(4))
insert [岗位表]
select 1,'前台' union all
select 2,'后厨'
--> 测试数据：[岗位明细表]
if object_id('[岗位明细表]') is not null drop table [岗位明细表]
go 
create table [岗位明细表]([job_id] int,[user_id] int)
insert [岗位明细表]
select 1,1 union all
select 1,2 union all
select 2,3 union all
select 2,4
--------------开始查询--------------------------



;WITH cte AS (select a.[user_name] ,c.job_name,c.job_id
from [用户表] a LEFT JOIN [岗位明细表] b ON a.[user_id]=b.[user_id]
LEFT JOIN [岗位表] c ON b.job_id=c.job_id)
select a.job_id,a.job_name,
stuff((select ','+[user_name] from cte b 
       where b.job_id=a.job_id and b.job_name=a.job_name 
       for xml path('')),1,1,'') 'user_name'
from cte a
group by  a.job_id,a.job_name
----------------结果----------------------------
/* 
job_id      job_name user_name
----------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           前台       小王,小张
2           后厨       小李,小周
*/

[阳泉酒家小当家]

是这样吗：

if object_id('[用户表]') is not null drop table [用户表]
go

create table [用户表]([user_id] int,[user_name] varchar(4))

insert [用户表]
select 1,'小王' union all
select 2,'小张' union all
select 3,'小李' union all
select 4,'小周'

if object_id('[岗位表]') is not null drop table [岗位表]
go 

create table [岗位表]([job_id] int,[job_name] varchar(4))
insert [岗位表]
select 1,'前台' union all
select 2,'后厨'


if object_id('[岗位明细表]') is not null drop table [岗位明细表]
go 
create table [岗位明细表]([job_id] int,[user_id] int)
insert [岗位明细表]
select 1,1 union all
select 1,2 union all
select 2,3 union all
select 2,4
go

select distinct
       job_id as 岗位编号,
       job_name as  岗位名称,   
       stuff(
			  (select ','+t3.user_name
			   from [岗位明细表] t2
			   inner join [用户表] t3
			           on t2.user_id = t3.user_id
			   where t2.job_id = t1.job_id
			   for xml path('')
			  ),
			  1,1,''
			) as 可用人员 
from [岗位表] t1
/*
岗位编号	岗位名称	 可用人员
1	    前台	     小王,小张
2	    后厨	     小李,小周
*/

[woshizhaoxuhui]

感谢大家，两种不同方法都可以实现，好好学习下！