有如下的两张表:
table_cn:
name count
a 1
b 3
c 2table_hk:
name count
a 2
b 5
d 3怎么得出这样的结果:
name count_cn count_hk
a 1 2
b 3 5
c 2 0
d 0 3数据库是oracle,请高手写一下,谢谢,最好能按count_cn+count_hk的总数从高到低排序。
谢谢,在线等...
table_cn:
name count
a 1
b 3
c 2table_hk:
name count
a 2
b 5
d 3怎么得出这样的结果:
name count_cn count_hk
a 1 2
b 3 5
c 2 0
d 0 3数据库是oracle,请高手写一下,谢谢,最好能按count_cn+count_hk的总数从高到低排序。
谢谢,在线等...
解决方案 »
- oracle数据备份问题
- .net访问oracle并向里面插入数据的问题
- oracle9i透明网关技术
- Oracle服务器莫名其妙地连不上了,从服务中看,服务还在正常运行,但是就是连不上,将服务重启后就好了,这是怎么回事?这种情况经常出现
- 关于时间段计算的复杂问题,请大家帮忙解决,多谢了!
- 如何将一个表设为readonly?
- 怎么样把oracle的表字段类型修改成clob类型
- 新手笨问题:我在sql plus用“打开”来打开一个SQL文件,是不是就自动执行了这个sql脚本?
- 请教一个简单的问题,关于参数。谢谢!
- sql联合查询的问题
- 求一sql语句
- 为什么我装oracle总是有错误弹出,卸载干净了。
select nvl(a.name,b.name),nvl(a.count,0) count_cn,nvl(b.count,0) count_hk from table_cn a,table_hk b
where a.name=b.name(+)
a.name = b.name(+)
union
select b.name,nvl(a.count,0) count_cn,nvl(b.count,0) count_hk from table_cn a,table_hk b where
a.name(+) = b.name;
/
select name,count_cn,count_hk from
(select a.name name,nvl(a.count,0) count_cn,nvl(b.count,0) count_hk,nvl(a.count,0)+nvl(b.count,0) from table_cn a,table_hk b where
a.name = b.name(+)
union
select b.name name,nvl(a.count,0) count_cn,nvl(b.count,0) count_hk,nvl(a.count,0)+nvl(b.count,0) from table_cn a,table_hk b where
a.name(+) = b.name
order by 4 desc,2 desc,3 desc);
select name,count_cn,count_hk from
(select nvl(a.name,b.name) name,nvl(a.count,0) count_cn,nvl(b.count,0) count_hk,nvl(a.count,0)+nvl(b.count,0)
from table_cn a full join table_hk b on a.name = b.name
order by 4 desc,2 desc,3 desc);
select name,sum(nvl(count,0)) count_cn,
(select sum(nvl(count,0))
from table_hk
where name = table_cn.name
group by name) count_hk
from table_cn
group by name
order by name,(count_cn + count_hk) desc;
语句已经有解答了,我就不写了
排序最后添上
order by count_cn+count_hk desc
列别名前面要定义好,要不出错
这种写法是什么意思?请高人解答一下,谢谢
比如两表以a.name = b.name条件连接
那么左连接会把b.name为空的记录也查出来,右连接查出a.name为空的记录
全连接是左右的合集
具体的你google查下外连接
4楼通过两个外连接再加上union来实现,并排序,思路清晰,但要排序耗费资源。
6楼的好!不过以前我还真的没有用过full join。
看这样行不行。
select nvl(T.name,t2.name) name,
nvl(t.count,0) count_cn,
nvl(t2.count,0)count_hk
from table_cn T full join table_hk T2
on T.name=T2.name;
SQL> select nvl(a.name, b.name), nvl(a.count,0) count_cn, nvl(b.count,0) count_h
k from table_cn a full join table_hk b on a.name=b.name order by count_cn+count_
HK desc;
a.count nvl(count_cn, 0),
nvl(b.count, 0) count_hk
from table_cn a
full join table_hk b on a.name = b.name;