如何在子查询mysql中添加查询限制

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<gt;小提琴 here

还请注意一些样本数据有多有用, 但是 由于没有提供这一点,我可能对这里看到的样本做出了错误的假设——如果样本数据与问题一起提供,总是更好的。

避免 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 

我认为这是可行的:

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 ,所以不需要 无效的 比较。

我仍然认为这个查询不起作用,因为您有一个双重嵌套的correlation子句。但这确实解决了你眼前的问题。

如果还是不行,就问 另一个 提出问题,提供示例数据、所需的结果,并解释要实现的逻辑。

没什么可以查我的答案。请尝试此操作,将子查询包装在另一个中以提供tmp表别名(tmp-bsn-u数据):

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