Mysql子查询帮助 [英] Mysql subquery help

问题描述

我有一个表，它有两种类型的记录 - 更新和结束(事件类型 - 列).

I have a table which had two types of records - update and end (EventType - Column).

更新记录有PID和网页详细信息，而结束记录有容量(上传和下载)和PID详细信息(没有网页详细信息:().这两种记录类型的公共字段是PID.

The Update record has the PID and webpage details where as the end record has the volume (upload and download) and PID detail (no web page detail:(). The common field for the both of the record types are PID.

现在从表中，我需要获取所有网站的 SUM(ULVolume+DLVolume)IE- 获取每个的总和(ULVo+DLVol)(获取每个的 PID(获取不同的网站))

Now from the table, I need to get the SUM(ULVolume+DLVolume) for all of the websites i.e - get sum(ULVo+DLVol) of each (get PID of each (get distinct websites))

最后，这就是我从一个查询中寻找的内容.在这里需要一些帮助.附言数据库庞大>10G，查询时间短，优先考虑.

At the end, this is what i am looking for from one single query.Need some help here. P.S. The database is huge >10G and query which takes less time wud be preferred.

预期输出

Website     Sum(ULVolume+DLVolume)
apple.com   112343XXXXX
google.com  121232XXXXX

更新记录:

+-----------------+--------+---------------+----------+----------+
| PID             | Event  | Web           | ULVolume | DLVolume |
+-----------------+--------+---------------+----------+----------+
| 199710687818416 | update | kaspersky.com |          |          |
| 199710687818417 | update | google.com    |          |          |
| 199710687818418 | update | yahoo.com     |          |          |
+-----------------+--------+---------------+----------+----------+

结束记录:

+-----------------+-------+------+----------+----------+
| PID             | Event | Web  | ULVolume | DLVolume |
+-----------------+-------+------+----------+----------+
| 199710687818416 | end   |      |     5187 |   309683 |
+-----------------+-------+------+----------+----------+

推荐答案

试试这个:

select
    u.website,
    sum(e.ULVolume + e. DLVolume) as volume
from mytable e
left join mytable u on u.PID = e.PID and u.event ='update'
where e.Event = 'end'
group by 1;

这篇关于Mysql子查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！