come表,nocome表,分别有字段tid,然后我要从come表和nocome表分别统计出来tid的次数。统计的结果要是这样
|——————————————————————————————————————————————||
| tid count(come.tid) count(nocome.tid)
|____________________________________________________________________________________________
|
|
|
|____________________________________________________________________________________________
|
|
|
|___________________________________________________________________________________________
|
|
|
|
|
|
|
|
|——————————————————————————————————————————————||
| tid count(come.tid) count(nocome.tid)
|____________________________________________________________________________________________
|
|
|
|____________________________________________________________________________________________
|
|
|
|___________________________________________________________________________________________
|
|
|
|
|
|
|
|
select tid,count(come.tid) as a,0 as b from come group by tid
union all
select tid,0 as a,count(nocome.tid) as b from nocome group by tid order by tid)
group by tid order by tid
create table come (
tid number(10)
);create table nocome(
tid number(10)
);
select nvl(a.tid,b.tid),count(a.tid),count(b.tid)
from come a full join nocome b on (a.tid=b.tid)
(select tid , count(1) nocome_tid from nocome group by tid) m
left join
(select tid , count(1) come_tid from come group by tid) n
on m.tid = n.tid如果是0的都不要,即需两表关联,则如下:
select m.* , n.come_tid from
(select tid , count(1) nocome_tid from nocome group by tid) m
inner join
(select tid , count(1) come_tid from come group by tid) n
on m.tid = n.tid
明白了,我后边又加了个having sum(b)>0就可以了。多谢。至此问题解决。我想看看还有没有别的办法。