部门和员工是一对多的关系。显示样式的问题
department表
id departcode
1 开始一部
2 开发二部
employ表
empid name departid
101 aa 1
102 bb 1
103 cc 2
开发一部 aa,bb
开发二部 cc
部门和员工是一对多的关系。
选出的结果是有多少个部门就显示几条记录，每条记录包括部门名称和该部门的所有员工姓名，通过逗号分开。
[解决办法]
create table department (id int, departcode nvarchar(30))
insert department
select 1,N '开始一部 '
union select 2,N '开发二部 '
create table employ(empid int, name varchar(10),departid int)
insert employ
select 101, 'aa ', 1
union all select 102, 'bb ', 1
union all select 103, 'cc ', 2
create function f(@id int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str= ' '
select @str=@str+ ', '+t.departcode
from (select b.departid,a.departcode from department a inner join employ b on a.id=b.departid) t
where t.departid=@id
set @str=stuff(@str,1,1, ' ')
return @str
end
select departid,dbo.f(departid) from employ group by departid
drop table department, employ
[解决办法]
create table employ(empid int,name char(2),departid int)
insert employ select 101, 'aa ',1 union
select 102, 'bb ',1 union
select 103, 'cc ',2
go
create function getstr(@content int)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str= ' '
select @str=@str+ ', '+[name] from employ where departid=@content
select @str=right(@str,len(@str)-1)
return @str
end
go
--调用：
create table department(id int,departcode varchar(10))
insert department select 1, '开始一部 ' union
select 2, '开发二部 '
go
select departcode,fusername
from department a
left join
(select departid,dbo.getstr(departid) fUserName from employ group by departid) b
on a.id = b.departid
---------------------
/*
departcode fusername
---------- --------------
开始一部 aa,bb
开发二部 cc
*/
[解决办法]
---上面的修改---
create table department (id int, departcode nvarchar(30))
insert department
select 1,N '开始一部 '
union select 2,N '开发二部 '
create table employ(empid int, name varchar(10),departid int)
insert employ
select 101, 'aa ', 1
union all select 102, 'bb ', 1
union all select 103, 'cc ', 2
create function f(@id int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str= ' '
select @str=@str+ ', '+t.[name]
from (select b.[name],b.departid,a.departcode from department a inner join employ b on a.id=b.departid) t
where t.departid=@id
set @str=stuff(@str,1,1, ' ')
return @str
end
select a.departcode,b.c1
from department a inner join (select departid,c1=dbo.f(departid) from employ group by departid) b on a.id=b.departid
