mysql查看多个重复表

pvabu6sv 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(292)

我正在尝试创建一个视图，其中包含多个表，这些表包含统一的日期/帐户数据，但数据观察值不同。

VISITS(Date, CompanyName, Visits)
SEARCHES(Date, CompanyName, Searches)
REPORTS(Date, CompanyName, Reports)

CREATE VIEW V AS 
    SELECT
        v.Date,
        v.CompanyName as Company,
        s.PageViews as Searches,
        r.PageViews as Reports
    FROM 
        Visits_r1 v
    LEFT JOIN
        Searches_r s ON v.CompanyName = s.CompanyName
    LEFT JOIN
        Reports_r r ON v.CompanyName = r.CompanyName
    WHERE
        v.Date = s.Date
    AND
        v.Date = r.Date;

然而，我遇到的问题是数据的重复；我想要按公司和日期列出的访问、搜索、报告的独特数量。这可能吗？

mysql Join duplicates View

来源：https://stackoverflow.com/questions/52769625/mysql-view-multiple-tables-duplicates

1条答案

按热度按时间

3z6pesqy1#

加入表后，需要按日期和公司名称分组，并使用sum（）聚合方法获取每个（日期，公司）组的总数。

CREATE VIEW V AS
    SELECT
        v.Date,
        v.CompanyName as Company,
        SUM(s.Searches) AS TotalSearches,
        SUM(r.Reports) AS TotalReports,
        SUM(v.Visits) AS TotalVisits
    FROM
        Visits AS v
    LEFT JOIN
        Searches AS s ON s.CompanyName = v.CompanyName AND s.Date = v.Date
    LEFT JOIN
        Reports AS r ON r.CompanyName = v.CompanyName AND r.Date = v.Date
    GROUP BY
        v.Date, v.CompanyName;

赞(0）回复(0）举报 2021-06-18

我来回答

mysql查看多个重复表

1条答案

相关问题

热门标签

最新问答