查询SQL语句

1、/*出口容量查询*/

SELECT a.`年`,a.`月`,a.`日`,a.`出口`,a.`物理带宽`,a.`峰值利用率`,a.`70-80拥塞链路数`,a.`80-90拥塞链路数`,a.`>90拥塞链路数`,a.`总拥塞链路数` FROM `容量` a

WHERE a.`年`='2016' AND a.`月`='9' AND a.`日`='1' AND a.`出口类型` !='地市城域网出口' AND a.`出口类型`!='地市CMNET出口';

2、/*链路组容量查询*/

SELECT SUBSTRING(b.`时间`,12) AS '时间',b.`出口`, b.`链路组`,SUM(b.`带宽`) AS '带宽',

ROUND(SUM(b.`峰值利用率`)/COUNT(b.`链路组`),2) AS '平均峰值利用率', b.`峰值利用率`>70 AND b.`峰值利用率`<80 AS '70%-80%链路数', b.`峰值利用率`>80 AND b.`峰值利用率`<90 AS '80%-90%链路数',

b.`峰值利用率`>90 AS '>90%链路数',b.`峰值利用率`>70 AS '拥塞（>70%)链路数合计'

FROM `单链路表` b WHERE b.`时间`='2016-09-02-2016-09-02' GROUP BY b.`链路组` ORDER BY b.`出口` DESC;

3、/*TOPN链路*/

SELECT SUBSTRING(c.`时间`,12) AS '时间',c.`出口`,c.`链路组`,c.`网元`,c.`本端端口`,c.`带宽`,

CONCAT(ROUND(c.`峰值利用率`,2),'%') AS '峰值利用率' FROM `单链路表` c ORDER BY c.`峰值利用率` DESC LIMIT 10;

4、/*月通报SQL*/

SELECT CONCAT(d.`年`,'-',d.`月`) AS '时间' ,d.`出口` , d.`开通带宽` ,

ROUND(SUM(d.`峰值利用率`)/COUNT(d.`峰值利用率`),2) AS '平均峰值利用率',

ROUND(SUM(d.`总拥塞链路数`)/COUNT( DISTINCT d.`日`)) AS '拥塞链路数',

CONCAT(ROUND(ROUND(SUM(d.`总拥塞链路数`)/COUNT( DISTINCT d.`日`))/d.`总链路数`*100,2),'%') AS '拥塞链路占比',COUNT( DISTINCT d.`日`) FROM `容量` d WHERE d.`年`='2016' AND d.`月`='9' AND d.`出口类型`!='地市城域网出口'

AND d.`出口类型`!='地市CMNET出口' GROUP BY d.`出口` DESC;

5、/*流量查询*/

SELECT CONCAT(a.`年`,'-',a.`月`,'-',a.`日`) AS '时间', a.`流量类型`,a.`流量名称`,a.`上行均值流速`,a.`下行均值流速`,

ROUND(a.`上行均值流速`+a.`下行均值流速`,2) AS '总流量'

FROM `流量` a WHERE a.`年`='2016' AND a.`月`='9' AND a.`日`='7';

6、/*流向查询*/

SELECT CONCAT(a.`年`,'-',a.`月`,'-',a.`日`) AS '时间',a.`业务类型`, CONCAT(ROUND(a.`省内IDC占比`*100,2),'%') AS '省内IDC', CONCAT(ROUND(a.`省内CACHECache`*100,2),'%') AS '省内Cache', CONCAT(ROUND(a.`省内其他占比`*100,2),'%') AS '省内其他', CONCAT(ROUND(a.`网内它省占比`*100,2),'%') AS '网内它省', CONCAT(ROUND(a.`网外资源占比`*100,2),'%') AS '网外资源' FROM `流向` a WHERE a.`年`='2016' AND a.`月`='1' AND a.`日`='1';

7、/*网间忙时查询*/

SELECT CONCAT(a.`年`,'-',a.`月`,'-',a.`日`) AS '时间',a.`集团骨干忙时网间流量`,a.`集团基准流量`

FROM `指标` a WHERE a.`年`='2016' AND a.`月`='9';

8、/*内容满足率查询*/

SELECT CONCAT(b.`年`,'-',b.`月`,'-',b.`日`) AS '时间',b.`地市下行流量`,b.`本省内容满足率`

FROM `指标` b WHERE b.`年`='2016' AND b.`月`='9';