mysql查看多个重复表

pvabu6sv  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(286)

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

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;

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

3z6pesqy

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;

相关问题