mysql-仅显示最常见的列组合&所有其他列组合显示0

请注意你不需要 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

你的问题有多个部分。如果你想要 0 值,则需要外部联接或相关子查询。我想你有一张所有比赛的桌子,但是 select distinct 上 entries 桌子也能用。

如果你想让大多数人崩溃 每辆车 你需要一个 汽车 中的标识符 crashes 表。我肯定有一个(这很有道理),但你不会在问题中描述它。我猜大概是 c_raceEntryCarId .

结果查询如下所示:

select e_racename, e.e_racedate,
       (select count(*)
        from crashes c
        where c.c_racename = e.e_racename and c.e_racedate = e.e_race_date 
        group by c.c_raceEntryCarId
        order by count(*) desc
        limit 1
       ) as most_crashes
from (select e.e_racename, e.e_racedate
      from entries e
      group by e.e_racename, e.e_racedate
     ) e;

如果您只想知道每次比赛的崩溃次数,那么子查询将有所不同:

select e_racename, e.e_racedate,
       (select count(*)
        from crashes c
        where c.c_racename = e.e_racename and c.e_racedate = e.e_race_date
       ) as most_crashes
from (select e.e_racename, e.e_racedate
      from entries e
      group by e.e_racename, e.e_racedate
     ) e;