我有两张桌子。一个叫做销售,另一个叫做Uren。“销售”表中的每个条目都代表一项销售,并带有销售日期(finalized_at),而“人人网”表中的每个条目代表代理商的工作班次,其中包含工作时间和工作日(日期)。这些表如下所示:
销售表
+-------+--------------+
| agent | finalized_at |
+-------+--------------+
| John | 01-01-2020 |
+-------+--------------+
| John | 02-01-2020 |
+-------+--------------+
| Mark | 01-01-2020 |
+-------+--------------+
| Peter | 01-01-2020 |
+-------+--------------+
| John | 04-01-2020 |
+-------+--------------+
小时表
+-------+-------+------------+
| agent | hours | datum |
+-------+-------+------------+
| John | 1 | 01-01-2020 |
+-------+-------+------------+
| John | 5 | 02-01-2020 |
+-------+-------+------------+
| Mark | 1 | 01-01-2020 |
+-------+-------+------------+
| Peter | 2 | 01-01-2020 |
+-------+-------+------------+
| John | 4 | 04-01-2020 |
+-------+-------+------------+
为了计算1月4日代理商的佣金,我必须考虑该月剩余时间代理商的表现。为此,我需要计算整个月的每小时销售额,但只计算1月4日进行销售的代理商的销售额。
我的查询如下所示:
SELECT
agent,
(SELECT COUNT(*)
FROM Sales
WHERE agent=Sales.agent
AND finalized_at BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59'
) / (
SELECT SUM(hours)
FROM Uren
WHERE datum BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59'
AND agent=Sales.agent
) as sph
FROM Sales
WHERE finalized_at BETWEEN '2020-01-04 00:00:00' AND '2020-01-04 23:59:59'
GROUP BY agent
ORDER by sph DESC;
由于某些原因,运行查询时,第二列(销售额)将返回“销售额”表中的总行数,而不是使用所述的where子句。
所以我的问题是。我究竟做错了什么?
尝试在内联视图中使用表别名(Sales2是我在下面使用的名称),以确保它引用的是正确的表。
SELECT
agent,
(SELECT COUNT(*)
FROM Sales Sales2
WHERE Sales2.agent=Sales.agent
AND Sales2.finalized_at BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59'
) / (
SELECT SUM(uren)
FROM Uren
WHERE datum BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59'
AND agent=Sales.agent
) as sph
FROM Sales
WHERE finalized_at BETWEEN '2020-01-04 00:00:00' AND '2020-01-04 23:59:59'
GROUP BY agent
ORDER by sph DESC;
另外,如果Uren
表中没有任何关联的记录,请注意除以0的可能性。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句