+ -
当前位置:首页 → 问答吧 → 请问这个sql语句该怎么写,通过表A id 查询表B记录,并列出。

请问这个sql语句该怎么写,通过表A id 查询表B记录,并列出。

时间:2011-11-24

来源:互联网

表A 
uid name age 
1 aaa 20 
2 bbb 21 
3 ccc 18

表 B 
id uid title times 
1 1 ggg 2005-11 
2 1 hhh 2005-11 
3 2 jjj 2005-11 
4 1 kkk 2005-11


希望通过一条sql语句查询出列出表A数据加上表A中uid在表B的记录数。
表A中还有个uid=3,那么它的记录数是0,这该怎么显示呢?

最后显示结果希望:

uid size(记录数)name age 
1 3 aaa 20 
2 1 bbb 21 
3 0 ccc 18

作者: wanhanqiu   发布时间: 2011-11-24

抛砖啦先……
SQL code
with a as (
select 1  uid1 , 'aaa' name,20 age from dual union all
select 2,'bbb',21 from  dual union all
select 3,'ccc',18 from  dual 
),
b as (
select 1 id,1 uid1,'ggg' title,'2005-11' times  from dual  union all
select 2 ,1,'hhh','2005-11' from dual  union all
select 3,2,'jjj','2005-11'from dual  union all
select 4,1,'kkk','2005-11' from dual  
)
select a.uid1,count( b.uid1),a.name,a.age  from a,b
where a.uid1=b.uid1(+)
group by a.uid1,a.name,a.age
order by a.uid1


--
1 3 aaa 20
2 1 bbb 21
3 0 ccc 18

作者: dws2004   发布时间: 2011-11-24

select t.id, count(ts.idb) as "size",t.name, t.age
  from a t
  left join b ts
  on (t.id = ts.id)
 group by t.id, t.name, t.age order by t.id;

作者: zuo0609   发布时间: 2011-11-24