通过关联表的值的总和对sql表进行排序

时间:2022-11-30 12:41:48

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:

我需要一个查询,它在一个请求中执行以下步骤:

  1. Find all clients for each network
  2. 查找每个网络的所有客户端

  3. Use ip's from this clients to get all connections for current network
  4. 使用此客户端的ip来获取当前网络的所有连接

  5. SUM traffic (Connections.in_bytes) for all ip's of current network to sort networks by this sum
  6. 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