连接查询问题
0
悬赏园豆：5
[已解决问题]
解决于 2013-01-25 14:19
创建表语句：
create table department
(
id int identity(1,1) primary key,
departmentName varchar(10) not null
)
create table userInfo
(
id int identity(1,1) primary key,
userName varchar(10) not null,
age int not null,
depId int foreign key references department(id)
)
insert into department values('行政')
insert into department values('IT')
insert into department values('商务')
insert into userInfo values('张三',20,1)
insert into userInfo values('李四',22,2)
insert into userInfo values('王五',35,1)
我需要的结果如下：
这怎么写啊，希望大家能给予帮助啊，高分答谢啊
最佳答案
0
1、建个方法
create or replace function test_c(str_in in varchar2) --分类字段 return varchar2 is str_list varchar2(4000) default null; --连接后字符串 str varchar2(20) default null; --连接符号 begin for x in (select uoo.username from department dett, userinfo uoo where dett.id = str_in and uoo.depid = dett.id) loop str_list := str_list || str || to_char(x.username); str := ', '; end loop; return str_list; end;
2、查一下：
select de.id,de.departmentname,test_c(de.id),(select count(uo.id) from userinfo uo where uo.depid=de.id) as num from department de
收获园豆：2
牛人啦，多谢
我执行创建方法报错啊
@Peter-Yu: 数据库不同吧，我用的是oracle，语法和数据类型可能有区别
其他回答(2)
0
试试下面的T-sql（sql Server 2005版本或以上版本可用），就是依据你的表结构写的，直接执行看效果：
1 select a.departmentName, 2 PersonCount=(select COUNT(*) from userInfo u where u.depId=a.id), 3 Products=replace( (select b.userName [data()] from userinfo b where b.depId=a.id order by userName 4 for xml path('')),' ','，') 5 from department a 6 order by departmentName
收获园豆：2
Products=replace( (select b.userName [data()] from userinfo b where b.depId=a.id order by userName 4 for xml path('')),' ','，')
能解释一下吗
@Peter-Yu: 呵呵，xml path就是将查出来的结果存成xml的形式。
然后，你分别把replace函数，[data()]去掉，看执行效果，你能理解的。
@jone_e: 多谢啊
@jone_e: 你是sql高手哦，呵呵
@jone_e: 你是sql高手哦，呵呵
0
--创建一个字符串连接函数 create function F_GetUserName(@depId int) returns varchar(8000) as begin declare @str varchar(8000) set @str='' select @str=@str+','+userName from userInfo where depId=@depId set @str=right(@str,len(@str)-1) return(@str) end --左接连查询 select a.departmentName, count=isnull(b.count,0), b.username from department a left join (select depId,COUNT(*) as count, dbo.F_GetUserName(depId) as username from userInfo group by depId) b on a.id=b.depId order by a.departmentName
收获园豆：1
