我认为这会奏效：

SELECT ka.kodeagent
       (SELECT COUNT(1)
        FROM bsn_data d
        WHERE d.periode >= '2018-12-01' AND
              d.periode < '2019-01-01' AND
              d.kodeupline2 = ka.kodeagent AND
              d.kodeagent = (SELECT d2.kodeagent
                             FROM bsn_data d2
                                  d2.periode >= '2018-12-01' AND
                                  d2.periode < '2019-01-01' AND
                                  d2.kodeupline2 = ka.kodeagent
                             GROUP BY d2.kodeagent
                             ORDER BY COUNT(1) DESC
                             LIMIT 1
                            )
        ) AS totps
FROM bsn_kode_agent ka
WHERE ka.fungsi = 'sales agent'
ORDER BY totps DESC;

笔记：
不要对日期使用字符串操作！使用适当的日期操作。
使用表别名并限定表名。 = 可以使用 limit ，尽管 in 不能。 COUNT() 不会再回来了 NULL ，因此不需要 NULL 比较。
我仍然不认为查询会起作用，因为您有一个双重嵌套的correlation子句。但这确实解决了你眼前的问题。
如果这仍然不起作用，问另一个问题，提供示例数据、期望的结果，并解释您试图实现的逻辑。

避免 IN ( SELECT ... ) 在这种情况下，把它变成一个 JOIN .
更改中间查询：

SELECT  COUNT(1)
    FROM  bsn_data
    WHERE  bsn_data.periode LIKE '2018-12-%%'
      AND  bsn_data.kodeupline2 = bsn_kode_agent.kodeagent
      AND  bsn_data.kodeagent IN (
        SELECT  bsn_data.kodeagent
            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  COUNT(1) DESC
            LIMIT  1 
                          )

到

SELECT  COUNT(1)
    FROM  
        ( SELECT  bsn_data.kodeagent
            FROM  bsn_data
            WHERE  bsn_data.periode >= '2018-12-01'
              AND  bsn_data.periode  < '2018-12-01' + INTERVAL 1 MONTH
              AND  bsn_data.kodeupline2 = bsn_kode_agent.kodeagent
            GROUP BY  bsn_data.kodeagent
            ORDER BY  COUNT(1) DESC
            LIMIT  1 
        ) AS x
    JOIN  bsn_data  ON x.kodeagent = bsn_data.kodeagent
    WHERE  bsn_data.periode >= '2018-12-01'
      AND  bsn_data.periode  < '2018-12-01' + INTERVAL 1 MONTH
      AND  bsn_data.kodeupline2 = bsn_kode_agent.kodeagent

索引：

bsn_data:  INDEX(kodeupline2, periode, kodeagent)  -- in this order
bsn_data:  (kodeagent)  -- is this the PRIMARY KEY?

但是等等！难道不能简化为

SELECT  COUNT(1) AS ct
    FROM  bsn_data
    WHERE  bsn_data.periode >= '2018-12-01'
      AND  bsn_data.periode <  '2018-12-01' + INTERVAL 1 MONTH
      AND  bsn_data.kodeupline2 = bsn_kode_agent.kodeagent
    GROUP BY  bsn_data.kodeagent
    ORDER BY  COUNT(1) DESC
    LIMIT  1

mariadb提供了“窗口函数”，我认为可以利用这些函数来实现这一点（也提到了前面的问题，似乎只需要“前3位”代理的计数）：

CREATE TABLE bsn_kode_agent(
   kodeagent VARCHAR(10) NOT NULL PRIMARY KEY
  ,fungsi    VARCHAR(40) NOT NULL
);

INSERT INTO bsn_kode_agent(kodeagent,fungsi) 
  VALUES
  ('a','sales agent')
, ('b','sales agent');

CREATE TABLE bsn_data(
   kodeagent   VARCHAR(1) NOT NULL
  ,kodeupline2 VARCHAR(2) NOT NULL
  ,periode     DATE  NOT NULL
);

INSERT INTO bsn_data(kodeagent,kodeupline2,periode) 
VALUES 
  ('a','b1','2018-12-01')
, ('a','b1','2018-12-01')
, ('a','b1','2018-12-01')
, ('a','c1','2018-12-01')
, ('a','c1','2018-12-01')
, ('a','c1','2018-12-01')
, ('a','d1','2018-12-01')
, ('a','d1','2018-12-01')
, ('a','e1','2018-12-01')
, ('a','f1','2018-12-01')
;

SELECT
    b.kodeagent
  , IFNULL( SUM( d.total ), 0 )  AS totps
FROM bsn_kode_agent AS b
LEFT JOIN (
        SELECT
            tableb.kodeupline2
          , tableb.kodeagent
          , tableb.total
          , ROW_NUMBER() OVER (PARTITION BY tableb.kodeagent
                                 ORDER BY tableb.total DESC) as rn
        FROM (
            SELECT
                bsn_data.kodeupline2
              , bsn_data.kodeagent
              , COUNT( 1 ) total
            FROM bsn_data
            WHERE  bsn_data.periode >= '2018-12-01'
              AND  bsn_data.periode <  '2018-12-01' + INTERVAL 1 MONTH
            GROUP BY
                bsn_data.kodeupline2
              , bsn_data.kodeagent
        ) AS tableb
    ) d ON d.kodeagent = b.kodeagent and d.rn <=3 
WHERE b.fungsi = 'sales agent'
group by
    b.kodeagent
ORDER BY
    totps DESC

kodeagent | totps
:-------- | ----:
a         |     8
b         |     0

下面：如果单独运行子查询结果。注意它是 rn 列只允许后续筛选具有最高计数的代理。

SELECT
            tableb.kodeupline2
          , tableb.kodeagent
          , tableb.total
          , ROW_NUMBER() OVER (PARTITION BY tableb.kodeagent
                                 ORDER BY tableb.total DESC) as rn
        FROM (
            SELECT
                bsn_data.kodeupline2
              , bsn_data.kodeagent
              , COUNT( 1 ) total
            FROM bsn_data
            WHERE  bsn_data.periode >= '2018-12-01'
              AND  bsn_data.periode <  '2018-12-01' + INTERVAL 1 MONTH
            GROUP BY
                bsn_data.kodeupline2
              , bsn_data.kodeagent
        ) AS tableb

kodeupline2 | kodeagent | total | rn
:---------- | :-------- | ----: | -:
b1          | a         |     3 |  1
c1          | a         |     3 |  2
d1          | a         |     2 |  3
e1          | a         |     1 |  4
f1          | a         |     1 |  5

db<>在这里摆弄
另外请注意，有一些样本数据是多么有用，但由于没有提供，我可能对这里看到的样本做了不正确的假设-如果随问题一起提供样本数据总是更好的。

没有任何东西可以检查我的答案。请尝试此操作，将子查询 Package 到另一个中以提供tmp表别名（tmp\u bsn\u data）：

SELECT kodeagent
 , IFNULL((
   SELECT COUNT(1)
   FROM bsn_data
   WHERE bsn_data.periode LIKE '2018-12-%%'
   AND bsn_data.kodeupline2 = bsn_kode_agent.kodeagent
   AND bsn_data.kodeagent IN( select tmp_bsn_data.kodeagent from (
       SELECT bsn_data.kodeagent
       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 COUNT(1) DESC LIMIT 1
       ) tmp_bsn_data
       )
   ), 0) AS totps
FROM bsn_kode_agent
WHERE fungsi = 'sales agent'
ORDER BY totps DESC