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

bemzoo • 5 年前 • 1550 次点击

我试图计算出一场比赛中撞车次数最多的一辆车,所以一辆车在一场比赛中可能会发生多起撞车事故(形象地说)。我有一个包含以下列的崩溃表:

c_raceName , c_raceDate , c_raceId 以及其他不相关的事情。

首先,为了唯一地识别一个种族 raceName 和 raceDate 一对。要唯一识别崩溃,必须在 raceId .

所以所有这些列实际上都属于另一个表 Entry 表。此表包含参加比赛的汽车的信息。此表包含以下列:

e_raceId , e_raceDate , e_raceName , e_raceEntryCarId , e_raceEntryDriverId .

我意识到并不是每一场比赛都会参加 Crash 桌子,所以我想我要提到 条目 中的表 撞车 桌子,或者其他地方。

尽管如此,如果还没有包含比赛项目的数据呢? raceNAMEY 和 赛马场 一对数据只存在于 Race 放置 r_raceName 和 r_raceDate 基本信息。

所以我想我应该做的是 SELECT 从 撞车 表,以及 JOIN 下到 条目 桌子,再往前 种族 真正得到一切的桌子 raceNAMEY 和 赛马场 对。尽管我不知道如何写一个条件,如果这些对不存在于 撞车 表,那么在我的输出中,我希望它们被赋予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

等。

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/43517

1550 次点击

文章 [ 2 ] | 最新文章 5 年前

• 1 楼

Used_By_Already 6 年前

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

• 2 楼

Gordon Linoff 6 年前

你的问题有多个部分。如果你想要 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;

登录后回复