首页 新闻 赞助 找找看

连接查询问题

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)

 

我需要的结果如下:

这怎么写啊,希望大家能给予帮助啊,高分答谢啊

Haydy的主页 Haydy | 初学一级 | 园豆:177
提问于:2013-01-25 11:22
< >
分享
最佳答案
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
vancegf | 菜鸟二级 |园豆:234 | 2013-01-25 13:57

牛人啦,多谢

Haydy | 园豆:177 (初学一级) | 2013-01-25 13:59

我执行创建方法报错啊

Haydy | 园豆:177 (初学一级) | 2013-01-25 14:01

@Peter-Yu: 数据库不同吧,我用的是oracle,语法和数据类型可能有区别

vancegf | 园豆:234 (菜鸟二级) | 2013-01-25 14:08
其他回答(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
jone_e | 园豆:1410 (小虾三级) | 2013-01-25 13:01

Products=replace( (select b.userName [data()] from userinfo b where b.depId=a.id order by userName 4 for xml path('')),' ',''
能解释一下吗

支持(0) 反对(0) Haydy | 园豆:177 (初学一级) | 2013-01-25 13:10

@Peter-Yu: 呵呵,xml path就是将查出来的结果存成xml的形式。

然后,你分别把replace函数,[data()]去掉,看执行效果,你能理解的。

支持(1) 反对(0) jone_e | 园豆:1410 (小虾三级) | 2013-01-25 13:13

@jone_e: 多谢啊

支持(0) 反对(0) Haydy | 园豆:177 (初学一级) | 2013-01-25 13:24

@jone_e: 你是sql高手哦,呵呵

支持(0) 反对(0) Haydy | 园豆:177 (初学一级) | 2013-01-25 13:26

@jone_e: 你是sql高手哦,呵呵

支持(0) 反对(0) Haydy | 园豆:177 (初学一级) | 2013-01-25 13:28
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
Rich.T | 园豆:3440 (老鸟四级) | 2013-01-25 14:11
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册