实例表结构和记录如下:
source dest length
192.168.5.1 192.168.2.3 27
192.168.5.1 192.168.2.5 466
192.168.5.2 192.168.2.5 2389
192.168.5.1 192.168.2.3 74
192.168.5.3 192.168.2.137 6890
192.168.5.2 192.168.2.137 389以上表为例,组合就有
source=192.168.5.1 and dest=192.168.2.3
source=192.168.5.1 and dest=192.168.2.5
source=192.168.5.1 and dest=192.168.2.137source=192.168.5.2 and dest=192.168.2.3
source=192.168.5.2 and dest=192.168.2.5
source=192.168.5.2 and dest=192.168.2.137source=192.168.5.3 and dest=192.168.2.3
source=192.168.5.3 and dest=192.168.2.5
source=192.168.5.3 and dest=192.168.2.137
要计算9种组合的记录数和length
需要按照这些不同的组合分别统计其记录数和length总量,怎么写语句?
source dest length
192.168.5.1 192.168.2.3 27
192.168.5.1 192.168.2.5 466
192.168.5.2 192.168.2.5 2389
192.168.5.1 192.168.2.3 74
192.168.5.3 192.168.2.137 6890
192.168.5.2 192.168.2.137 389以上表为例,组合就有
source=192.168.5.1 and dest=192.168.2.3
source=192.168.5.1 and dest=192.168.2.5
source=192.168.5.1 and dest=192.168.2.137source=192.168.5.2 and dest=192.168.2.3
source=192.168.5.2 and dest=192.168.2.5
source=192.168.5.2 and dest=192.168.2.137source=192.168.5.3 and dest=192.168.2.3
source=192.168.5.3 and dest=192.168.2.5
source=192.168.5.3 and dest=192.168.2.137
要计算9种组合的记录数和length
需要按照这些不同的组合分别统计其记录数和length总量,怎么写语句?
解决方案 »
- 为什么 pl/sql desc 不能用 还有 就是每次create 表老报错 说缺失右括号
- sql,三个表查询问题。
- oracle 8.1.7 误删除一些表的数据后,如何恢复?求高手解答
- sql查询效率
- 100分求几条语句
- 请教一简单SQL
- 在ORACLE中的,PL/SQL中如何获得前10条数据呢
- 请大家帮忙:我在Oracle9i建库的时候到46%的时候就会出现错误,错误提示为:ORA-12571:TNS:包写入程序失败,请大虾指教!
- 急:如何建立这个视图
- Oracle 11G 安装时出现问题,请大神指点
- 在windows 的MFC中用OCCI通过连接字符串连接Windows上的oracle10g 报告ora12560
- ORACLE 查询的速度很快,把查询的数据插入到表中就很慢,什么原因?
(select source, dest
from (select distinct source from tab) a,
(select distinct dest from tab) b ) c, tab d
where c.source = d.source(+)
and c.dest = d.dest(+)
select from c.source, c.dest, count(*),sum(length_user)
(select source, dest
from (select distinct source from tab) a,
(select distinct dest from tab) b ) c, tab d
where c.source = d.source(+)
and c.dest = d.dest(+)
group by c.source, c.dest
SELECT COUNT(*),SUM(LENGTH) FROM TAB GROUP BY SOURCE,DEST方案二:不存在的分组在结果中出现,得到的值为0
SELECT A.SOURCE,B.DEST,COUNT(LENGTH),NVL(SUM(LENGTH),0)
FROM (SELECT DISTINCT SOURCE FROM TAB) A
CROSS JOIN (SELECT DISTINCT DEST FROM TAB) B
LEFT JOIN TAB C ON A.SOURCE = C.SOURCE AND B.DEST = C.DEST
GROUP BY A.SOURCE,B.DEST
ORDER BY A.SOURCE,B.DEST;
SELECT COUNT(*),SUM(LENGTH) FROM TAB GROUP BY SOURCE,DEST方案二:不存在的分组在结果中出现,得到的值为0
SELECT A.SOURCE,B.DEST,COUNT(LENGTH),NVL(SUM(LENGTH),0)
FROM (SELECT DISTINCT SOURCE FROM TAB) A
CROSS JOIN (SELECT DISTINCT DEST FROM TAB) B
LEFT JOIN TAB C ON A.SOURCE = C.SOURCE AND B.DEST = C.DEST
GROUP BY A.SOURCE,B.DEST
ORDER BY A.SOURCE,B.DEST;