现有表A,其中ID为主键,自加1的
结构如下:
table A
ID USER_ID
1 1
2 1
3 2
4 2
5 2
6 3
7 4
8 4
9 5
还有一个表B
结构如下:
table B
ID USER_ID
1 1
2 2
3 2
4 3
5 3
6 3
7 4
求解:
在A中出现的USER_ID和其出现的次数,和本USER_ID在B中出现的次数
结果应该是这样
USER_ID TIME_A TIME_B
1 2 1
2 3 2
3 1 3
4 2 1
5 1 0
求SQL
结构如下:
table A
ID USER_ID
1 1
2 1
3 2
4 2
5 2
6 3
7 4
8 4
9 5
还有一个表B
结构如下:
table B
ID USER_ID
1 1
2 2
3 2
4 3
5 3
6 3
7 4
求解:
在A中出现的USER_ID和其出现的次数,和本USER_ID在B中出现的次数
结果应该是这样
USER_ID TIME_A TIME_B
1 2 1
2 3 2
3 1 3
4 2 1
5 1 0
求SQL
(select USER_ID,count(*) cnt from tablea) a full outer join
(select USER_ID,count(*) cnt from tableb) b
on a.user_id=b.user_id
(select USER_ID,count(*) cnt from tablea group by user_id) a full outer join
(select USER_ID,count(*) cnt from tableb group by user_id) b
on a.user_id=b.user_id
count(decode(ftype,'A',user_id,null)) time_A,
count(decode(ftype,'B',user_id,null)) time_B
from (
select 'A' ftype, user_id from A
union all
select 'B' ftype,user_id from B
) t
group by user_id
from a ,(select distinct b.user_id, count(b.user_id) cntb from b group by user_id) t
where a.user_id=t.user_id(+)
group by a.user_id,cntb
order by a.user_id;
from
(select user_id,count(*) timea from tbA group by user_id) a,
(select user_id,count(*) timeb from tbB group by user_id) b
where a.user_id=b.user_id