我的数据库包含以下表格：
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个日期对（子查询的WHERE子句中的1个和主查询的WHERE子句中的1个）。我希望能够在单个位置以（xxxx-xx-xx）输入日期对，并使用该日期对以要求的格式在两个区域中填充日期。不幸的是，我无法更改数据或表。
我已经搜索了很长时间，但似乎找不到能够转移到我的情况的答案。我尝试了许多排列，这是我能想到的唯一可以给我预期结果的排列。如果您有任何疑问或需要任何其他信息，请告诉我。任何帮助是极大的赞赏。谢谢。
最佳答案
这只是我的脑袋，也许您可以先将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
不知道这是否行得通，但希望能给您一些想法