sql-group-by在创建新字段时的最大值

ddarikpa 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(308)

我有一个数据库和一个叫做 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 joins；但是，由于我必须创建新字段 total 使用聚合函数( SUM )还有另一个功能( DATEDIFF )我尝试实现这些解决方案并没有成功，而不仅仅是查询现有的字段。
我希望我的查询，使我接近将只需要一个小的修改，以获得正确的解决方案。

sql mysql greatest-n-per-group

来源：https://stackoverflow.com/questions/53531400/sql-max-value-from-a-group-by-when-creating-a-new-field

1条答案

按热度按时间

x759pob21#

有连字符的 - 在列名中（也是减号运算符）是一个非常糟糕的主意。请考虑将其替换为下划线字符 _ .
一种可能的方法是使用派生表。一个派生表用于确定 total 在一组主id和位置id上。使用另一个派生表根据主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

赞(0）回复(0）举报 2021-06-18

我来回答

sql-group-by在创建新字段时的最大值

1条答案

相关问题

热门标签

最新问答