在创建新字段时从组中获得最大值

Question

我有一个名为BOOKINGS的数据库，该表包含以下值

main-id     place-id     start-date    end-date
1                  1     2018-8-1      2018-8-8
2                  2     2018-6-6      2018-6-9
3                  3     2018-5-5      2018-5-8
4                  4     2018-4-4      2018-4-5
5                  5     2018-3-3      2018-3-10
5                  1     2018-1-1      2018-1-6
4                  2     2018-2-1      2018-2-10
3                  3     2018-3-1      2018-3-28
2                  4     2018-4-1      2018-4-6
1                  5     2018-5-1      2018-5-15
1                  3     2018-6-1      2018-8-8
1                  4     2018-7-1      2018-7-6
1                  1     2018-8-1      2018-8-18
1                  2     2018-9-1      2018-9-3
1                  5     2018-10-1     2018-10-6
2                  5     2018-11-1     2018-11-5
2                  3     2018-12-1     2018-12-25
2                  2     2018-2-2      2018-2-19
2                  4     2018-4-4      2018-4-9
2                  1     2018-5-5      2018-5-23

我需要做的是，对于每个main-id，我需要为每个place-id找到最大的总天数。基本上，我需要确定每个主id花在哪里的时间最多。

然后必须将这些信息放到视图中，因此不幸的是，我不能使用临时表。

使我最接近的查询是

CREATE VIEW `MOSTTIME` (`main-id`,`place-id`,`total`) AS
SELECT `BOOKINGS`.`main-id`, `BOOKINGS`.`place-id`, SUM(DATEDIFF(`end-date`, `begin-date`)) AS `total`
FROM `BOOKINGS`
GROUP BY `BOOKINGS`.`main-id`,`RESERVATION`.`place-id`

产生的结果：

main-id            place-id                total

1                   1                     24
1                   2                     18
1                   5                     5
2                   1                     2
2                   2                     20
2                   4                     9
3                   1                     68
3                   2                     24
3                   3                     30
4                   1                     5
4                   2                     10
4                   4                     1
5                   1                     19
5                   2                     4
5                   5                     7

我需要的是每个不同的total的最大main-id

main-id            place-id                total
1                   1                     24
2                   2                     20
3                   1                     68
4                   2                     10
5                   1                     19

我已经浏览了大量类似的帖子，推荐了一些类似的东西，比如self联接；但是，由于我必须使用聚合函数( total )和另一个函数(DATEDIFF)来创建新的字段，而不是仅仅查询现有的字段，所以我实现这些解决方案的尝试都没有成功。

我希望，我的查询，使我接近将只需要一个小的修改，以获得正确的解决方案。

Answer 1

在列名中使用连字符- (也是减号运算符)是一个非常糟糕的想法。请考虑用下划线字符_替换它。

一种可能的方法是使用导出表。一个派生表用于确定一组主id和place id上的total。另一个派生表用于根据主id从它们中获取最大值。然后，我们可以重新加入，只得到与最大值相对应的行。

CREATE VIEW `MOSTTIME` (`main-id`,`place-id`,`total`) AS
SELECT b1.main_id, b1.place_id, b1.total 
FROM 
(
  SELECT `main-id` AS main_id, 
         `place-id` AS place_id, 
         SUM(DATEDIFF(`end-date`, `begin-date`)) AS total 
  FROM BOOKINGS 
  GROUP BY main_id, place_id
) AS b1
JOIN
(
  SELECT dt.main_id, MAX(dt.total) AS max_total 
  FROM 
  (
    SELECT `main-id` AS main_id, 
           `place-id` AS place_id, 
           SUM(DATEDIFF(`end-date`, `begin-date`)) AS total 
    FROM BOOKINGS 
    GROUP BY main_id, place_id
  ) AS dt 
  GROUP BY dt.main_id 
) AS b2 
  ON b1.main_id = b2.main_id AND 
     b1.total = b2.max_total 

MySQL 8+解决方案将利用Row_Number()功能：

CREATE VIEW `MOSTTIME` (`main-id`,`place-id`,`total`) AS
SELECT b.main_id, b.place_id, b.total 
FROM 
(
  SELECT dt.main_id, 
         dt.place_id, 
         dt.total 
         ROW_NUMBER() OVER (PARTITION BY dt.main_id 
                            ORDER BY dt.total DESC) AS row_num                            
  FROM 
  (
    SELECT `main-id` AS main_id, 
           `place-id` AS place_id, 
           SUM(DATEDIFF(`end-date`, `begin-date`)) AS total 
    FROM BOOKINGS 
    GROUP BY main_id, place_id
  ) AS dt 
  GROUP BY dt.main_id 
) AS b
WHERE b.row_num = 1