I have 2 tables:
我有2张桌子:
Table 1: Connections
Columns: id, ip, in_bytes, out_bytes, protocol
Table 2: Clients
Columns: id, ip, network
I want to list networks sorted by amount of traffic.
我想列出按流量排序的网络。
I need a query which does the following steps in one request:
我需要一个查询,它在一个请求中执行以下步骤:
- Find all clients for each network
- Use ip's from this clients to get all connections for current network
- SUM traffic (Connections.in_bytes) for all ip's of current network to sort networks by this sum
查找每个网络的所有客户端
使用此客户端的ip来获取当前网络的所有连接
SUM流量(Connections.in_bytes)用于当前网络的所有IP,以此总和对网络进行排序
Can anyone help to create the sql query?
任何人都可以帮助创建SQL查询?
1 个解决方案
#1
1
Simple join with a SUM?
与SUM简单连接?
SELECT a.id, a.ip, a.network, SUM(b.in_bytes + b.out_bytes) AS traffic
FROM Client a
INNER JOIN Connections b
ON a.ip = b.ip
GROUP BY a.id, a.ip, a.network
ORDER BY traffic
#1
1
Simple join with a SUM?
与SUM简单连接?
SELECT a.id, a.ip, a.network, SUM(b.in_bytes + b.out_bytes) AS traffic
FROM Client a
INNER JOIN Connections b
ON a.ip = b.ip
GROUP BY a.id, a.ip, a.network
ORDER BY traffic