首页 文章

mySQL - 仅显示最常见和所有其他列组合显示0

提问于
浏览 次
0

我试图计算任何一辆汽车在一场比赛中发生最多的碰撞事故，因此一辆汽车在一场比赛中可能会发生多次碰撞（比喻） . 我有一个包含以下列的崩溃表：

c_raceName ， c_raceDate ， c_raceId 等无关紧要 .

首先，为了唯一地识别种族，它出现在 raceName 和 raceDate 对中 . 要唯一地识别崩溃，您必须将此对与 raceId 一起使用 .

因此，所有这些列实际上属于另一个表，即我们的 Entry 表 . 该表包含进入比赛的汽车信息 . 该表包含以下列：

e_raceId ， e_raceDate ， e_raceName ， e_raceEntryCarId ， e_raceEntryDriverId .

我已经意识到并不是每个种族都会进入 Crash 表，所以我相信我要么必须引用 Crash 表中的 Entry 表，要么反过来 .

此外，如果数据尚未包含在竞赛条目中，并且 raceName 和 raceDate 数据对仅存在于保存 r_raceName 和 r_raceDate 基本信息的 Race 表上，该怎么办？

所以我认为我可能要做的是从 Crash 表中 SELECT ，然后在 JOIN 到 Entry 表，再进一步到 Race 表以真正获得所有 raceName 和 raceDate 对 . 虽然我不知道如何编写一个条件，说明如果这些对在 Crash 表中不存在，那么在我的输出中我希望它们的值为0，或者使它们出现在我的输出中事实上 .

所以我已经走到了这一步：

SELECT DISTINCT l.c_raceName AS raceName, 
l.c_raceDate AS raceDate, 
COUNT( l.c_raceId) AS mostCrashes 

FROM Crashes l
GROUP BY l.c_raceId
;

产生的输出：

raceName | raceDate | mostCrashes
---------------------------------
Race 1   |2018/01/21| 1
Race 1   |2018/01/21| 3
...      | ...      | ...

所以我已经能够将它们分成3和1，如上所示，尽管我只希望显示最多的崩溃，所以3行 . 此外，我希望所有没有任何崩溃的比赛都显示为0，而不是根本不显示 . 所以我正在寻找的输出是：

raceName | raceDate | mostCrashes
---------------------------------
Race 1   |2018/01/21| 3
Race 2   |2018/01/21| 5
Race 1   |2018/09/11| 0
Race 1   |2016/03/14| 0

等等

mysql sql count distinct

2 回答

  • 1

    你的问题有很多部分 . 如果需要 0 值，则需要外连接或相关子查询 . 我想你有一张包含所有比赛的 table ，但是 entries 表上的 select distinct 也可以 .

    然后，如果您希望每辆车发生最多崩溃，则需要在 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;
    回复于
  • 1

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

    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或更高版本，则可以使用公用表表达式（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
;

    另一种方法，如果只想要一行，则按计算列（按降序排序）对结果进行排序，并将结果限制为一行 . ÿ

    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

    edit

    CREATE TABLE崩溃（
    c_raceName varchar（40）NOT NULL
    ，c_raceDate varchar（40）NOT NULL
    ）;
    ✓

    INSERT INTO崩溃（c_raceName，c_raceDate）
    VALUES
    （'种族1'，'2018/01/21'）
    ，（'种族1'，'2018/01/21'）
    ，（'种族1'，'2018/01/21'）
    ，（'种族2'，'2018/01/21'）
    ，（'种族2'，'2018/01/21'）
    ，（'种族2'，'2018/01/21'）
    ，（'种族2'，'2018/01/21'）
    ，（'种族2'，'2018/01/21'）
    ，（'种族1'，'2018/09/11'）
    ，（'Race 1'，'2016/03/14'）;
    ;
    ✓

    选择
    l.c_raceName AS raceName
    ，l.c_raceDate AS raceDate
    ，COUNT（l.c_raceDate）AS mostCrashes
    FROM Crashes l
    通过...分组
    l.c_raceName
    ，l.c_raceDate
    有COUNT（l.c_raceDate）= 0
    OR COUNT（l.c_raceDate）=（
    选择
    MAX（最多崩溃）
    来自（
    选择
    l.c_raceName AS raceName
    ，l.c_raceDate AS raceDate
    ，COUNT（l.c_raceDate）AS mostCrashes
    FROM Crashes l
    通过...分组
    l.c_raceName
    ，l.c_raceDate
    ）sq
    ）
    订购
    mostCrashes
    ;
    raceName | raceDate | mostCrashes
    ：------- | ：--------- | ----------：
    比赛2 | 2018/01/21 |五

    与cte
    AS（
    选择
    l.c_raceName AS raceName
    ，l.c_raceDate AS raceDate
    ，COUNT（l.c_raceDate）AS mostCrashes
    FROM Crashes l
    通过...分组
    l.c_raceName
    ，l.c_raceDate
    ）
    选择

    来自cte
    WHERE mostCrashes =（SELECT MAX（mostCrashes）FROM cte）
    或者mostCrashes = 0
    订购
    mostCrashes
    ;
    raceName | raceDate | mostCrashes
    ：------- | ：--------- | ----------：
    比赛2 | 2018/01/21 |五

    db <>小提琴here

    回复于

相关问题