mysql - 加入两个表以从一个表中获取 COUNT 并从另一个表中获取 SUM

Question

我正在从事一个项目，用户在该项目中处理报告并在数据库中输入他们工作的详细信息。我的数据库结构有两个表:

tbl_reports - 此表包含已执行工作的所有详细信息

    report_id    user_id    date        country            status    -----------------------------------------------------------------------    0001         abc        2014-05-04  USA                checked    0002         abc        2014-05-04  USA                checked     0003         abc        2014-05-05  India              checked     0004         lmn        2014-05-04  USA                checked    0005         lmn        2014-05-04  India              checked    0006         xyz        2014-05-06  Taiwan             checked    

tbl_time - this table contains all details on time repoted by the users, date and country wise

    id    user_id    date        country     time (hrs)    ----------------------------------------------------    01    abc        2014-05-04  USA         4    02    abc        2014-05-05  India       2    03    lmn        2014-05-04  USA         3     04    lmn        2014-05-04  India       2    05    opq        2014-05-05  Belgium     4    

As you can see users "abc and "lmn" have tracked all their tasks appropriately while user "xyz" has not tracked his time yet and user "opq" has tracked his time but has no records of reports he has worked on.

Now out of this I want to extract details of this team GROUPING BY "date" and "country" as below:

    date        country      total_report_count        total_time_count    -----------------------------------------------------------------------    2014-05-04  India        1                         2    2014-05-04  USA          3                         7    2014-05-05  Belgium      0                         4    2014-05-05  India        1                         2    2014-05-06  Taiwan       1                         0    

Which means irrespective of which user has tracked his reports or time, I need to generate team report for worked done in which country on which date , its counts and total time tracked.

Now I was able to find total_time_count report using below code:

    CREATE VIEW vw_teamreport AS
    SELECT
    tb1.date , tb1.country,
    SUM(tb1.time) AS total_time_count 
    FROM tbl_time tb1 
    LEFT JOIN tbl_reports tb2 
    ON tb2.report_id IS NULL
    GROUP BY tb1.date, tb1.country
    ORDER BY tb1.date, tb1.country;

需要帮助来完成问题，我正在使用 MYSQL(如果需要 FULL JOIN，则不支持 FULL JOIN 关键字)

Answer 1

因为没有FULL JOIN，您需要一个查询来从这两个表的 UNION 中提取所有不同的日期/国家/地区组合。或者，您将需要一些其他查询来生成日期和国家/地区的完整列表。将此查询称为 A。

您需要编写两个单独的聚合查询。一个将按日期和国家/地区汇总小时数，另一个将按日期和国家/地区汇总报告。将这些查询称为 B 和 C。

然后你需要做

  SELECT whatever, whatever
    FROM (
           /*query A*/
         ) AS a
    LEFT JOIN (
           /*query B*/
         ) AS b ON a.date=b.date AND a.country=b.country
    LEFT JOIN (
           /*query C*/
         ) AS c ON a.date=c.date AND a.country=c.country

这将生成一个正确汇总的报告，其中包含您需要的所有行，以及缺少汇总数据的 NULL。

编辑抱歉，忘记了嵌套查询 View 限制。您需要创建四个 View ，一个用于每个子查询，一个用于连接查询。所以它将是:

 CREATE VIEW dates_countries AS
      SELECT DISTINCT `date`, country FROM tbl_time
       UNION 
      SELECT DISTINCT `date`, country FROM tbl_reports;

 CREATE VIEW time_totals AS
      SELECT `date`, country, SUM(time) AS tot
        FROM tbl_time
       GROUP BY `date`, country

 CREATE VIEW report_totals AS
      SELECT `date`, country, COUNT(*) AS tot
        FROM tbl_reports
       GROUP BY `date`, country

最后是这个 View 。

CREATE VIEW team_report AS
     SELECT a.`date`, a.country,
            c.tot AS total_report_count,
            b.tot AS total_time_count
       FROM dates_countries AS a
       LEFT JOIN time_totals AS b  ON a.`date` = b.`date` AND a.country = b.country
       LEFT JOIN repoorts_totals AS r  ON a.`date` = r.`date` AND a.country = r.country;

当你需要一个 View 时，你没有太多选择。