SELECT
kodeagent,
(SELECT IFNULL(SUM(total),0) FROM
(SELECT bsn_data.kodeagent,
IFNULL(COUNT(1),0) total
FROM bsn_data
WHERE bsn_data.periode LIKE '2018-12-%%'
AND bsn_data.kodeupline2=bsn_kode_agent.kodeagent
GROUP BY bsn_data.kodeagent ORDER BY total DESC limit 3)AS tableb) AS totps
FROM bsn_kode_agent WHERE fungsi='sales agent' ORDER BY totps desc
得到结果
错误代码: 1054'where子句'中的未知列'bsn_kode_agent.kodeagent'
我该如何解决?我希望as bsn_kode_agent.kodeagent
可以检测为列。
发布于 2018-12-12 14:54:10
您当前的查询中有太多“图层”
SELECT ... # layer 1
, (
SELECT ... # layer 2
FROM
(SELECT ... # layer 3
FROM ...
WHERE ...
AND ... = bsn_kode_agent.kodeagent # from layer 1
) AS tableb
) AS totps
FROM bsn_kode_agent
“第1层”表只能在一层向下引用。因此,您需要简化子查询,以下可能有效:
SELECT kodeagent
, IFNULL((
SELECT COUNT(DISTINCT bsn_data.kodeagent)
FROM bsn_data
WHERE bsn_data.periode LIKE '2018-12-%%'
AND bsn_data.kodeupline2 = bsn_kode_agent.kodeagent
), 0) AS totps
FROM bsn_kode_agent
WHERE fungsi = 'sales agent'
ORDER BY totps DESC
ps:我不确定使用LIKE '2018-12-%%'
是否是一种过滤日期范围的有效方法。
https://stackoverflow.com/questions/-100006269
复制相似问题