MYSQL Join weeks table with Sales 和 Group by Salesman and Weeks

Question

我目前有一个数据库来跟踪销售团队的销售情况。我有一个查询将提取每个销售员及其相关的总数，但我希望按周对此进行分割，然后如果可能的话在一周内汇总显示。

我当前使用的查询是:

SELECT ROUND(SUM(n.newBalance), 2) AS newB, u.username
  FROM (
    SELECT 
        j.leadid AS custid, 
        WEEK(j.convertdate) AS weeks,
        j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
    FROM jobbooktbl j
    WHERE j.convertdate BETWEEN '2017-01-02' AND '2017-07-31' 
    AND j.status IN (4,6,7,8,11)
    ) n
    JOIN assignmentstbl a USING (custid)
    JOIN usertbl u USING (userid)
    GROUP BY a.userid

这将返回以下内容(按销售员分组):

+-----------+-------------+
| salesman  | Sales Total |
+-----------+-------------+
| salesman1 |    1850     |
| salesman2 |    1170     |
+-----------+-------------+

我希望完成的是按周分割并返回以下内容(按周分组，然后按销售员分组):

+-----------+--------+-------------+
| salesman  | weekNo | sales total |
+-----------+--------+-------------+
| salesman1 |   1    |      0      |
| salesman2 |   1    |      0      |
| salesman1 |   2    |     100     |
| salesman2 |   2    |     100     |
| salesman1 |   3    |    1300     |
| salesman2 |   3    |      0      |
| salesman1 |   4    |     450     |
| salesman2 |   4    |    1070     |
| salesman1 |   5    |      0      |
| salesman2 |   5    |      0      |
+-----------+--------+-------------+

如果可能的话，还有像这样的汇总(按销售员按周分组，运行总计/汇总):

+-----------+--------+-------------+
| salesman  | weekNo | sales total |
+-----------+--------+-------------+
| salesman1 |   1    |      0      |
| salesman2 |   1    |      0      |
| salesman1 |   2    |     100     |
| salesman2 |   2    |     100     |
| salesman1 |   3    |    1400     |
| salesman2 |   3    |     100     |
| salesman1 |   4    |    1850     |
| salesman2 |   4    |    1170     |
| salesman1 |   5    |    1850     |
| salesman2 |   5    |    1170     |
+-----------+--------+-------------+

这是目前的架构:

CREATE TABLE weekstbl
  (`weekNo` int, `weekStart` date)
;

INSERT INTO weekstbl
  (`weekNo`, `weekStart`)
VALUES
  (1, '2017-01-02'),
  (2, '2017-01-09'),
  (3, '2017-01-16'),
  (4, '2017-01-23'),
  (5, '2017-01-30')
;

CREATE TABLE jobbooktbl
  (`leadid` int, `convertdate` date, `price` int, `status` int)
;

INSERT INTO jobbooktbl
  (`leadid`, `convertdate`, `price`, `status`)
VALUES
  (1, '2017-01-16', 500, 4),
  (2, '2017-01-24', 620, 6),
  (3, '2017-01-17', 800, 7),
  (4, '2017-01-26', 900, 11),
  (5, '2017-01-10', 200, 4)
;


CREATE TABLE assignmentstbl
    (`custid` int, `userid` int)
;

INSERT INTO assignmentstbl
    (`custid`, `userid`)
VALUES
    (1, 1),
    (2, 2),
    (3, 1),
    (4, 2),
    (4, 1),
    (5, 1),
    (5, 2)
;

CREATE TABLE usertbl
    (`userid` int, `username` varchar(25))
;

INSERT INTO usertbl
    (`userid`,`username`)    
VALUES
    (1,'salesman1'),
    (2,'salesman2')
;

这是一个 SQLFIDDLE包含上述所有信息。

我尝试过 LEFT JOINing 两个表，但无济于事。我真的是 SQL 的初学者，所以这有点超出我的控制范围。我还创建了 weekstbl 只是因为我不知道如何为销售员没有任何值(value)的星期返回 0，这可能没有必要。

试验:

试验 1

SELECT ROUND(SUM(n.newBalance), 2) AS newB, weeks, u.username
  FROM (
    SELECT 
        j.leadid AS custid, 
        w.weekno AS weeks,
        j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
    FROM jobbooktbl j
    LEFT JOIN weekstbl w on w.weekNo=WEEK(j.convertdate) 
    AND j.convertdate BETWEEN '2017-01-02' AND '2017-07-31'
    AND j.status IN (4,6,7,8,11)
    ) n
    JOIN assignmentstbl a USING (custid)
    JOIN usertbl u USING (userid)
    GROUP BY weeks, a.userid

这返回了以下结果集，其中不包括第 1、3(对于 salesman2)或 5 周的 0:

+-----------+--------+-------------+
| salesman  | weekNo | sales total |
+-----------+--------+-------------+
| salesman1 |   2    |     100     |
| salesman2 |   2    |     100     |
| salesman1 |   3    |    1300     |
| salesman1 |   4    |     450     |
| salesman2 |   4    |    1070     |
+-----------+--------+-------------+

试验 2

SELECT ROUND(SUM(n.newBalance), 2) AS newB, weeks, u.username
  FROM (
    SELECT 
        j.leadid AS custid, 
        w.weekno AS weeks,
        j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
    FROM jobbooktbl j
    join weekstbl w on j.convertdate between weekstart and date(weekstart + interval 6 day )
    WHERE j.convertdate BETWEEN '2017-01-02' AND '2017-07-31' 
    AND j.status IN (4,6,7,8,11)
    ) n
    JOIN assignmentstbl a USING (custid)
    JOIN usertbl u USING (userid)
    GROUP BY weeks, a.userid

这返回了以下结果集(不包括第 1、3(对于 salesman2)或 5 周的 0):

 +-----------+--------+-------------+
 | salesman  | weekNo | sales total |
 +-----------+--------+-------------+
 | salesman1 |   2    |     100     |
 | salesman2 |   2    |     100     |
 | salesman1 |   3    |    1300     |
 | salesman1 |   4    |     450     |
 | salesman2 |   4    |    1070     |
 +-----------+--------+-------------+

试验 3:

SELECT * FROM (
 SELECT ROUND(SUM(n.newBalance), 2) AS newB, u.username,weeks
      FROM (
        SELECT 
            j.leadid AS custid, 
            WEEK(j.convertdate) AS weeks,
            j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
        FROM jobbooktbl j
        WHERE j.convertdate BETWEEN '2017-01-02' AND '2017-07-31' 
        AND j.status IN (4,6,7,8,11)
        ) n
        JOIN assignmentstbl a USING (custid)
        JOIN usertbl u USING (userid)
        GROUP BY a.userid,n.weeks
        ORDER BY newB DESC
    )INNERTABLE
    LEFT JOIN weekstbl CL ON CL.weekNo=INNERTABLE.weeks

这返回了以下结果集(不包括第 1、3(对于 salesman2)或 5 周的 0):

 +-----------+--------+-------------+
 | salesman  | weekNo | sales total |
 +-----------+--------+-------------+
 | salesman1 |   2    |     100     |
 | salesman2 |   2    |     100     |
 | salesman1 |   3    |    1300     |
 | salesman1 |   4    |     450     |
 | salesman2 |   4    |    1070     |
 +-----------+--------+-------------+

试验 4:

与这个更接近一点

SELECT 
    w.weekNo, COALESCE(ROUND(SUM(n.newBalance), 2),0) AS newB, n.username
FROM 
    weekstbl w
LEFT JOIN (
  SELECT 
      j.leadid AS custid,
      j.convertdate AS sold,
      u.username AS username,
      j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
  FROM 
      jobbooktbl j
  JOIN assignmentstbl a ON j.leadid = a.custid
  JOIN usertbl u ON u.userid = a.userid
) n 
ON 
    w.weekNo = WEEK(n.sold)
GROUP BY
    n.username, w.weekNo
ORDER BY 
    w.weekNo

这返回了以下结果集(第 1 周和第 5 周返回 0，但未识别出销售员并且在第 3 周没有为 salesman2 返回 0):

 +-----------+--------+-------------+
 | salesman  | weekNo | sales total |
 +-----------+--------+-------------+
 |  (null)   |   1    |      0      |
 | salesman1 |   2    |     100     |
 | salesman1 |   2    |     100     |
 | salesman1 |   3    |    1300     |
 | salesman1 |   4    |     450     |
 | salesman2 |   4    |    1070     |
 |  (null)   |   5    |      0      |
 +-----------+--------+-------------+

Answer 1

我添加了一个与 weekstbl 的连接。您可以查看以下查询。我希望这有帮助。

SELECT ROUND(SUM(n.newBalance), 2) AS newB, weeks, u.username
  FROM (
    SELECT 
        j.leadid AS custid, 
        w.weekno AS weeks,
        j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
    FROM jobbooktbl j
    join weekstbl w on j.convertdate between weekstart and date(weekstart + interval 6 day )
    WHERE j.convertdate BETWEEN '2017-01-02' AND '2017-07-31' 
    AND j.status IN (4,6,7,8,11)
    ) n
    JOIN assignmentstbl a USING (custid)
    JOIN usertbl u USING (userid)
    -- WHERE a.userid=5
    GROUP BY weeks, a.userid
    ORDER BY newB DESC

这是一个更新的答案。

select userid, username, week, year, fvalue
from ( select sub3.*, 
if(@previous = userid, @value1 := @value1 + value, @value1 := value ) fvalue,
@previous := userid
from (select distinct ut.userid, ut.username, 
week(date) as week,year(date) as year ,coalesce(sub2.newB,0) as value
from ( SELECT (CURDATE() - INTERVAL c.number DAY) AS date
FROM (SELECT singles + tens + hundreds number FROM 
( SELECT 0 singles
UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
) singles JOIN 
(SELECT 0 tens
UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
) tens  JOIN 
(SELECT 0 hundreds
UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
) hundreds
ORDER BY number DESC) c  ) abc 
cross join usertbl ut
left join (
  SELECT ROUND(SUM(n.newBalance), 2) AS newB, weeks, years,a.userid, u.username
  FROM (
    SELECT 
        j.leadid AS custid, 
        w.weekno AS weeks,
    year(weekstart) as years,
        j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
    FROM jobbooktbl j
    join weekstbl w on j.convertdate between weekstart and date(weekstart + interval 6 day )
    WHERE j.convertdate BETWEEN '2017-01-02' AND '2017-07-31' 
    AND j.status IN (4,6,7,8,11)
    ) n
    JOIN assignmentstbl a USING (custid)
    JOIN usertbl u on u.userid = a.userid
    GROUP BY weeks, a.userid
) sub2 on sub2.userid = ut.userid 
and weeks = week(date)
and years = year(date)
where date between '2017-01-02' AND '2017-07-31' 
order by userid,year(date), week(date) ) sub3 ) sub4
order by year, week, userid

注意:我建议创建 dim_time 表来存储日期的所有相关信息。

解释:

1) 子查询名称:abc这将根据您的输入生成周和年。之后，将您的结果与 usertbl 交叉连接。你想要的行数最终输出。现在我们根据您的要求增加了值(value)。

2) 子查询名称:sub2
这会产生您需要的结果，但不会显示 0 值。

3) 现在 1 左加入 2这给你的结果是(按周分组，然后按销售员分组)。按子句更改顺序只是为了获得预期的输出。
这成为你的sub3。这是必要的，因为我们必须将前一个值与下一个值相加。

4) 创建变量@previous 和@value1因为我们已经根据 userid 对结果进行了排序。现在第一行来了，它检查以下条件。然后它转到其他部分，因为它不匹配，将用户 ID 存储在@previous 中。它现在执行第二行，因为条件已满足，它会将先前的值添加到下一行。类似地它会添加您的结果，直到新的用户 ID 出现。

条件:

MySQL (@previous = userid,  @value1 := @value1 + value ,@value1 := value)

if @previous = userid 
then @value1 := @value1 + value 
else @value1 := value; 

希望对您有所帮助。