mysql - 跨主查询和子查询的表连接

Question

我的数据库包含以下表格:

TABLE SALESCOUNT
-----------------------------------
| id | startWeek  | repID | sales |
-----------------------------------
| 1  | FY13M01W01 | 0001  |   4   |
| 2  | FY13M01W01 | 0002  |   8   |
| 3  | FY13M01W02 | 0001  |   5   |
| 4  | FY13M01W02 | 0002  |   9   |
...

TABLE SALES
----------------------------------------------------
| id | salesRepID | salesDate  | division  | team  |
----------------------------------------------------
| 1  |   0001     | 2013-01-01 |   divA    | teamA |
| 2  |   0002     | 2013-01-01 |   divC    | teamA |
| 3  |   0001     | 2013-01-01 |   divD    | teamD |
| 4  |   0002     | 2013-01-08 |   divB    | teamC |
...

TABLE DEPT
-------------------------------
| id | repID | region | dept  |
-------------------------------
| 1  | 0001  |   01   |   A   |
| 2  | 0002  |   02   |   B   |
| 3  | 0001  |   C    |   A   |
| 4  | 0002  |   02   |   B   |
...

TABLE GEO
-----------------------------------
| id | geo   | regionA | regionB  |
-----------------------------------
| 1  | geoA  |   01    |     A    |
| 2  | geoA  |   02    |     B    |
| 3  | geoB  |   03    |     C    |
| 4  | geoB  |   04    |     D    |
...

TABLE FISCALWEEK
--------------------------------
| id | salesDate  | startWeek  |
--------------------------------
| 1  | 2013-01-01 | FY13M01W01 |
| 2  | 2013-01-08 | FY13M01W02 |
...

以下查询返回所需的结果:

SELECT y.startWeek, SUM(y.sales), dept.dept, geo.geo
FROM salesCount as y,
(
    SELECT DISTINCT a.salesRepID
    FROM sales AS a
    WHERE (a.team IN ("teamA", "teamB", "teamC)
        AND a.saleDate BETWEEN "2013-01-01" AND "2013-06-30"
        AND a.division IN ("divA", "divB", "divC")
) as x
INNER JOIN dept
    ON x.salesRepID = dept.repID
INNER JOIN geo
    ON dept.region = geo.regionA OR dept.region = geo.regionB
WHERE x.salesRepID = y.repID
    AND y.startWeek BETWEEN "FY13M01W01" AND "FY13M06W04"
GROUP BY y.startWeek, geo.geo, dept.dept
ORDER BY y.startWeek ASC, geo.geo ASC, dept.dept ASC

我的目标是利用 FISCALWEEK 表生成 2 个日期对(1 个在子查询的 WHERE 子句中，1 个在主查询的 WHERE 子句中)。我希望能够在一个位置输入日期对(xxxx-xx-xx)，并使用它以所需格式填充两个区域的日期。不幸的是，我无法更改数据或表格。

我已经搜索了很长一段时间，似乎找不到可以转移到我的情况的答案。我尝试了多种排列，这是我能想出的唯一一种可以给我带来预期结果的排列。如果您有任何疑问或需要任何其他信息，请告诉我。任何帮助是极大的赞赏。谢谢。

Answer 1

这只是我的想法，也许您可​​以从将 saleDate 提取到外部查询中开始:

SELECT y.startWeek, SUM(y.sales), dept.dept, geo.geo
FROM salesCount as y,
(
    SELECT DISTINCT a.salesRepID, a.saleDate
    FROM sales AS a
    WHERE (a.team IN ("teamA", "teamB", "teamC)
        AND a.division IN ("divA", "divB", "divC")
) as x
INNER JOIN dept
    ON x.salesRepID = dept.repID
INNER JOIN geo
    ON dept.region = geo.regionA OR dept.region = geo.regionB
WHERE x.salesRepID = y.repID
    AND y.startWeek BETWEEN "FY13M01W01" AND "FY13M06W04"
    AND x.saleDate BETWEEN "2013-01-01" AND "2013-06-30"
GROUP BY y.startWeek, geo.geo, dept.dept
ORDER BY y.startWeek ASC, geo.geo ASC, dept.dept ASC

然后从那里您可以查看加入 FISCALWEEK 表，例如:

SELECT y.startWeek, SUM(y.sales), dept.dept, geo.geo
FROM salesCount as y,
(
    SELECT DISTINCT a.salesRepID, a.saleDate
    FROM sales AS a
    WHERE (a.team IN ("teamA", "teamB", "teamC)
        AND a.division IN ("divA", "divB", "divC")
) as x
INNER JOIN dept
    ON x.salesRepID = dept.repID
INNER JOIN geo
    ON dept.region = geo.regionA OR dept.region = geo.regionB
INNER JOIN fiscalweek
    ON y.startWeek = fiscalweek.startWeek
WHERE x.salesRepID = y.repID
    AND y.startWeek BETWEEN "FY13M01W01" AND "FY13M06W04"
    AND x.saleDate = fiscalweek.salesDate 
GROUP BY y.startWeek, geo.geo, dept.dept
ORDER BY y.startWeek ASC, geo.geo ASC, dept.dept ASC

不确定这是否有效，但希望能给您一些想法