php - 具有挑战性的 html/PHP 报告的 MySQL 查询

Question

这是对优秀 MySQL 编码员的挑战。我们非常感谢有关创建在 html/PHP 页面中显示的 MySQL 报告的查询方面的帮助。

我们知道如何收集数据并存储它，并拥有 PHP 报告布局，但不知道如何构建查询。

我们有两个 MySQL 表——“visitors”和“buyers”。

1. 访客表 – 用于通过以下方式访问我们网站的访客标识 sales_rep 和众多之一的特定 URL他们自己创建营销代码。
2. 买家表 - 供购买商品的人使用。

访问者可以通过以下链接访问我们的网站，例如:

www.sales_rep.website.com/index.php?marketing=code1/code2/code3

在“访问者”表中，我们包含三个字段:

• 销售代表
• 日期
• marketing_CODE –(在本例中为字符串中的“code1/code2/code3”，但每个 sales_rep 可以根据其广告事件拥有多个 marketing_CODE；例如“code1a/codeTX/codeM2”和“market- z/ad-123")

当访问者购买商品时，我们会将其添加到“买家”表中，其中包括:

• 销售代表
• 日期
• marketing_TYPE –(可以是“URL”、“推荐”或其他类型)
• marketing_VALUE –(如果 marketing_TYPE 为网址，则为 marketing_CODE)

我们需要查看任何指定月份的每个特定销售代表的报告，按营销代码排序，仅适用于 marketing_TYPE ==“URL”。

此报告需要显示每个 marketing_CODE 的访问者和买家事件的每周分割，如下所示:

为了确保清晰，在上表中，在第 1 周，8 位访问者通过“code1/code2/”链接进入网站，其中 5 位访问者购买了商品。

我们尝试使用以下内容开始查询，但不知道该从哪里开始:

1. $chosen_month = $_POST['chosen_month'];
2. 在 sales_rep 上从访问者中选择 * INNER JOIN 买家 WHERE marketing_type = "URL"AND sales_rep = '$sales_rep' AND date('Y-m') = $'chosen_month' ;
3. 使用 while 语句，我们将为找到的每条记录生成表格单元格。使用它，我们知道如何列出每条记录，但我们无法弄清楚如何一次显示非不同记录，显示其计数，然后显示不同记录。

感谢您的时间和帮助。

Answer 1

这是我对您的查询的建议。

首先，我需要根据日期字段找出周数，因此我使用 this question 中的答案。 :

SELECT  WEEK(dateField, 5) -
        WEEK(DATE_SUB(dateField, INTERVAL DAYOFMONTH(dateField) - 1 DAY), 5) + 1

从现在开始，我将把上面的结果称为@week，以使事情更具可读性:)

然后，我从其中一张表开始，尝试查看销售代表每个月有多少访客:

select rep, 
  , SUM(CASE @week WHEN 1 THEN 1 ELSE 0 END) as Week1
  , SUM(CASE @week WHEN 2 THEN 1 ELSE 0 END) as Week2
  , SUM(CASE @week WHEN 3 THEN 1 ELSE 0 END) as Week3
  , SUM(CASE @week WHEN 4 THEN 1 ELSE 0 END) as Week4
  , COUNT(*) as Total
from visitors
group by rep

现在我们获得了此信息，我们将针对买家使用相同的信息，然后按如下方式连接这两个子查询的结果:

select v.rep
, v.Week1 , b.Week1
, v.Week2 , b.Week2
, v.Week3 , b.Week3
, v.Week4 , b.Week4
, v.Total, b.Total
from (
     select rep, 
        , SUM(CASE @week WHEN 1 THEN 1 ELSE 0 END) as Week1
        , SUM(CASE @week WHEN 2 THEN 1 ELSE 0 END) as Week2
        , SUM(CASE @week WHEN 3 THEN 1 ELSE 0 END) as Week3
        , SUM(CASE @week WHEN 4 THEN 1 ELSE 0 END) as Week4
        , COUNT(*) as Total
     from visitors
     group by rep) as v
     left join (select rep, 
                 , SUM(CASE @week WHEN 1 THEN 1 ELSE 0 END) as Week1
                 , SUM(CASE @week WHEN 2 THEN 1 ELSE 0 END) as Week2
                 , SUM(CASE @week WHEN 3 THEN 1 ELSE 0 END) as Week3
                 , SUM(CASE @week WHEN 4 THEN 1 ELSE 0 END) as Week4
                 , COUNT(*) as Total
                from visitors
                group by rep) as b on v.rep = b.rep

这应该就是您要找的!

下面是根据您的情况更新的与上面相同的代码:

select v.rep
    , v.Week1 , b.Week1
    , v.Week2 , b.Week2
    , v.Week3 , b.Week3
    , v.Week4 , b.Week4
    , v.Total, b.Total
from (
        select rep
          , SUM(CASE WEEK(the_date, 5) -
            WEEK(DATE_SUB(the_date, INTERVAL DAYOFMONTH(the_date) - 1 DAY), 5) + 1
            WHEN 1 THEN 1 ELSE 0 END) as Week1
      , SUM(CASE WEEK(the_date, 5) -
            WEEK(DATE_SUB(the_date, INTERVAL DAYOFMONTH(the_date) - 1 DAY), 5) + 1
            WHEN 2 THEN 1 ELSE 0 END) as Week2
      , SUM(CASE WEEK(the_date, 5) -
        WEEK(DATE_SUB(the_date, INTERVAL DAYOFMONTH(the_date) - 1 DAY), 5) + 1
            WHEN 3 THEN 1 ELSE 0 END) as Week3
      , SUM(CASE WEEK(the_date, 5) -
        WEEK(DATE_SUB(the_date, INTERVAL DAYOFMONTH(the_date) - 1 DAY), 5) + 1
            WHEN 4 THEN 1 ELSE 0 END) as Week4
      , COUNT(*) as Total
        from visitors
        where sales_rep = '$sales_rep' AND date('Y-m') = $'chosen_month'
        group by rep ) as v
  left join (
        select rep
          , SUM(CASE WEEK(the_date, 5) -
            WEEK(DATE_SUB(the_date, INTERVAL DAYOFMONTH(the_date) - 1 DAY), 5) + 1
            WHEN 1 THEN 1 ELSE 0 END) as Week1
      , SUM(CASE WEEK(the_date, 5) -
            WEEK(DATE_SUB(the_date, INTERVAL DAYOFMONTH(the_date) - 1 DAY), 5) + 1
            WHEN 2 THEN 1 ELSE 0 END) as Week2
      , SUM(CASE WEEK(the_date, 5) -
        WEEK(DATE_SUB(the_date, INTERVAL DAYOFMONTH(the_date) - 1 DAY), 5) + 1
            WHEN 3 THEN 1 ELSE 0 END) as Week3
      , SUM(CASE WEEK(the_date, 5) -
        WEEK(DATE_SUB(the_date, INTERVAL DAYOFMONTH(the_date) - 1 DAY), 5) + 1
            WHEN 4 THEN 1 ELSE 0 END) as Week4
      , COUNT(*) as Total
        from buyers
        where marketing_type = "URL" AND sales_rep = '$sales_rep' AND date('Y-m') = $'chosen_month'
        group by rep) as b on v.rep = b.rep