php - SQL根据另一个列值得到不同的列结果

Question

有这样的表:

`id`     `race_day`  `racer_nr`  `racer_name`           `racer_class`    `start_time`               `arrive_time`               `spent_time`     `total`     `yellow_card`   `bonus`     `penalties`     `comment`
1           1           313       'racer name 1'         ''              '2018-02-04 10:00:00'      '2018-02-04 10:57:00'       '00:57:00'       '00:57:00'     0               0           0               ''
2           1           135       'racer name 2'         ''              '2018-02-04 10:00:00'      '2018-02-04 13:59:55'       '03:59:55'       '03:59:55'     0               0           5               ''
3           1           55        'racer name 3'         ''              '2018-02-04 10:00:00'      '2018-02-04 11:59:50'       '01:59:50'       '01:59:50'     0               0           0               ''
4           1           315       'racer name 4'     ''              '2018-02-04 10:00:00'      '2018-02-04 10:01:40'       '00:01:40'       '00:01:40'     0               20          3600            ''
17          2           313       'racer name 1'         ''              '2018-02-05 10:00:00'      '2018-02-05 10:57:00'       '00:57:00'       '00:57:00'     0               0           0               ''
18          2           135       'racer name 2'         ''              '2018-02-05 10:00:00'      '2018-02-05 13:59:55'       '03:59:55'       '03:59:55'     0               0           0               ''
19          2           55        'racer name 3'         ''              '2018-02-05 10:00:00'      '2018-02-05 11:59:50'       '01:59:50'       '01:59:50'     0               0           0               ''
20          2           315       'racer name 4'     ''              '2018-02-05 10:00:00'      '2018-02-05 10:01:40'       '00:01:40'       '00:01:40'     0               0           0               ''
21          3           313       'racer name 1'         ''              '2018-02-06 10:00:00'      '2018-02-06 10:57:00'       '00:57:00'       '00:57:00'     0               0           0               ''
22          3           135       'racer name 2'         ''              '2018-02-06 10:00:00'      '2018-02-06 13:59:55'       '03:59:55'       '03:59:55'     0               0           0               ''
23          3           55        'racer name 3'         ''              '2018-02-06 10:00:00'      '2018-02-06 11:59:50'       '01:59:50'       '01:59:50'     0               0           0               ''
24          3           315       'racer name 4'     ''              '2018-02-06 10:00:00'      '2018-02-06 10:01:40'       '00:01:40'       '00:01:40'     0               15          3600            ''
25          4           313       'racer name 1'         ''              '2018-02-07 10:00:00'      '2018-02-07 10:57:00'       '00:57:00'       '00:57:00'     0               0           0               ''
26          4           135       'racer name 2'         ''              '2018-02-07 10:00:00'      '2018-02-07 13:59:55'       '03:59:55'       '03:59:55'     0               0           0               ''
27          4           55        'racer name 3'         ''              '2018-02-07 10:00:00'      '2018-02-07 11:59:50'       '01:59:50'       '01:59:50'     0               0           0               ''
28          4           315       'racer name 4'     ''              '2018-02-07 10:00:00'      '2018-02-07 10:01:40'       '00:01:40'       '00:01:40'     0               0           0               ''
29          5           313       'racer name 1'         ''              '2018-02-08 10:00:00'      '2018-02-08 10:57:00'       '00:57:00'       '00:57:00'     0               0           0               ''
30          5           135       'racer name 2'         ''              '2018-02-08 10:00:00'      '2018-02-08 13:59:55'       '03:59:55'       '03:59:55'     0               0           0               ''
31          5           55        'racer name 3'         ''              '2018-02-08 10:00:00'      '2018-02-08 11:59:50'       '01:59:50'       '01:59:50'     0               0           0               ''
32          5           315       'racer name 4'     ''              '2018-02-08 10:00:00'      '2018-02-08 10:01:40'       '00:01:40'       '00:01:40'     0               0           0               ''

和这样的sql查询:

SELECT 
    id, 
    race_day, 
    racer_nr, 
    racer_name, 
    arrive_time, 
    spent_time, 
    sec_to_time(SUM(time_to_sec(spent_time)+penalties-bonus)) AS total_time_spent, 
    SUM(penalties) AS total_penalties, 
    SUM(bonus) as total_bonus, 
    yellow_card 
FROM `race_days` 
WHERE 
     `race_day` = 1 OR `race_day` = 2 OR `race_day` = 3 OR `race_day` = 4 OR `race_day` = 5
GROUP BY racer_nr 
ORDER BY total_time_spent ASC

我得到了我想要的所有数据..但如果我想获得不同race_day的到达时间，我只能得到第一个日期(race_day =1)

即计算 race_day = 2 的总数我的查询将如下所示:

SELECT 
    id, 
    race_day, 
    racer_nr, 
    racer_name, 
    arrive_time, 
    spent_time, 
    sec_to_time(SUM(time_to_sec(spent_time)+penalties-bonus)) AS total_time_spent, 
    SUM(penalties) AS total_penalties, 
    SUM(bonus) as total_bonus, 
    yellow_card 
FROM `race_days` 
WHERE 
     `race_day` = 1 OR `race_day` = 2
GROUP BY racer_nr 
ORDER BY total_time_spent ASC

我无法获得race_day = 2的到达时间(以及开始时间)..

所以我想要的race_day = 2结果应该是这样的:

`id`, `race_day`, `racer_nr`, `racer_name`, `arrive_time`, `spent_time`, `total_time_spent`, `total_penalties`, `total_bonus`, `yellow_card`
4, 1, 315, 'racer name 4', '2018-02-05 10:01:40', '00:01:40', '01:03:00', '3600', '20', 0
1, 1, 313, 'racer name 1', '2018-02-05 10:57:00', '00:57:00', '01:54:00', '0', '0', 0
3, 1, 55, 'racer name 3', '2018-02-05 11:59:50', '01:59:50', '03:59:40', '0', '0', 0
2, 1, 135, 'racer name 2', '2018-02-05 13:59:55', '03:59:55', '07:59:55', '5', '0', 0

如有任何建议，我们将不胜感激

Answer 1

select 子句应始终与聚合查询中的 group by 子句匹配。所以你应该从以下开始:

SELECT racer_nr, 
       sec_to_time(SUM(time_to_sec(spent_time)+penalties-bonus)) AS total_time_spent, 
       SUM(penalties) AS total_penalties, 
       SUM(bonus) as total_bonus
FROM race_days rd
WHERE race_day in (1, 2, 3, 4, 5)
GROUP BY racer_nr 
ORDER BY total_time_spent ASC;

我不知道您想要其他(非聚合)列是什么。但是，它们应该是聚合函数的参数或在 group by 子句中。

例如，如果您希望每个 race_day 都有一个单独的行，您可以这样做:

SELECT racer_nr, race_day, MIN(arrive_time),
       sec_to_time(SUM(time_to_sec(spent_time)+penalties-bonus)) AS total_time_spent, 
       SUM(penalties) AS total_penalties, 
       SUM(bonus) as total_bonus
FROM race_days rd
WHERE race_day in (1, 2, 3, 4, 5)
GROUP BY racer_nr, race_day
ORDER BY total_time_spent ASC;