Used_By_Already

请注意你不需要 select distinct 使用时 group by .

SELECT
    l.c_raceName  AS raceName
  , l.c_raceDate  AS raceDate
  , COUNT( * )    AS mostCrashes
FROM Crashes l
GROUP BY
    l.c_raceName  
  , l.c_raceDate
HAVING COUNT( * ) = 0
OR COUNT( *) = (
    SELECT
        MAX( mostCrashes )
    FROM (
        SELECT
            l.c_raceName        AS raceName
          , l.c_raceDate        AS raceDate
          , COUNT( l.c_raceId ) AS mostCrashes
        FROM Crashes l
        GROUP BY
            l.c_raceName  
          , l.c_raceDate
    ) sq
)
ORDER BY
    mostCrashes
;

如果您使用的是mysql 8或更高版本,那么可以使用公共表表达式(common table expression,cte)来简化它。

WITH cte
AS (
    SELECT
        l.c_raceName AS raceName
      , l.c_raceDate AS raceDate
      , COUNT( * )   AS mostCrashes
    FROM Crashes l
    GROUP BY
        l.c_raceName  
      , l.c_raceDate
)
SELECT
    *
FROM cte
WHERE mostCrashes = ( SELECT MAX( mostCrashes ) FROM cte )
OR mostCrashes  = 0
ORDER BY
    mostCrashes
;

如果只需要一行,另一种方法是按计算列对结果排序(降序),并将结果限制为一行。Y

SELECT
    l.c_raceName        AS raceName
  , l.c_raceDate        AS raceDate
  , COUNT( l.c_raceId ) AS mostCrashes
FROM Crashes l
GROUP BY
    l.c_raceName  
  , l.c_raceDate
ORDER BY
    mostCrashes DESC
LIMIT 1

编辑

CREATE TABLE Crashes(
   c_raceName varchar(40)  NOT NULL
  ,c_raceDate varchar(40)  NOT NULL
);

✓

INSERT INTO Crashes(c_raceName,c_raceDate) 
    VALUES
      ('Race 1','2018/01/21')
    , ('Race 1','2018/01/21')
    , ('Race 1','2018/01/21')
    , ('Race 2','2018/01/21')
    , ('Race 2','2018/01/21')
    , ('Race 2','2018/01/21')
    , ('Race 2','2018/01/21')
    , ('Race 2','2018/01/21')
    , ('Race 1','2018/09/11')
    , ('Race 1','2016/03/14');
    ;

艾斯

SELECT
    l.c_raceName        AS raceName
  , l.c_raceDate        AS raceDate
  , COUNT( l.c_raceDate ) AS mostCrashes
FROM Crashes l
GROUP BY
    l.c_raceName
  , l.c_raceDate
HAVING COUNT( l.c_raceDate ) = 0
OR COUNT( l.c_raceDate ) = (
    SELECT
        MAX( mostCrashes )
    FROM (
        SELECT
            l.c_raceName        AS raceName
          , l.c_raceDate        AS raceDate
          , COUNT( l.c_raceDate ) AS mostCrashes
        FROM Crashes l
        GROUP BY
            l.c_raceName
          , l.c_raceDate
    ) sq
)
ORDER BY
    mostCrashes
;

raceName | raceDate   | mostCrashes
:------- | :--------- | ----------:
Race 2   | 2018/01/21 |           5

WITH cte
AS (
    SELECT
        l.c_raceName        AS raceName
      , l.c_raceDate        AS raceDate
      , COUNT( l.c_raceDate ) AS mostCrashes
    FROM Crashes l
    GROUP BY
        l.c_raceName
      , l.c_raceDate
)
SELECT
    *
FROM cte
WHERE mostCrashes = ( SELECT MAX( mostCrashes ) FROM cte )
OR mostCrashes  = 0
ORDER BY
    mostCrashes
;

总状花序总状花序多数
:———————————————————
比赛2 2018/01/21 5

DB<gt;小提琴 here

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

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