mysql 无法在sql中为我的子查询创建分组依据

bihw5rsg 于 2023-01-04 发布在 Mysql

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

我有这样的疑问：

select *
from(
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_date desc) rn,
        sum(date_diff('day', start_date, end_date)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = false
)
where rn = 1
union  
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_of_life desc) rn,
        sum(date_diff('day', start_date, end_of_life)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = true
)
where rn = 1)
where record_id = 146 and TotalDays <= 60 and TotalDays >= 0
group by record_id, hood_id, city_id, TotalDays

并且输出其：
| 记录标识|发动机罩_id|城市标识|总天数|
| - ------| - ------| - ------| - ------|
| 一百四十六|三个|十二|三十|
| 一百四十六|三个|十二|十个|
我想要这个输出：
| 记录标识|发动机罩_id|城市标识|总天数|
| - ------| - ------| - ------| - ------|
| 一百四十六|三个|十二|四十|
有人有解决这个问题好办法吗？

mysql

来源：https://stackoverflow.com/questions/74986599/cannot-create-group-by-to-my-sub-query-in-sql

1条答案

按热度按时间

0ve6wy6x1#

您只需使用SUM函数添加TotalDays。

select record_id, hood_id, city_id, SUM(TotalDays) as TotalDays
from(
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_date desc) rn,
        sum(date_diff('day', start_date, end_date)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = false
)
where rn = 1
union  
select record_id, hood_id, city_id, TotalDays
from (
    select *, 
        row_number() over(partition by record_id order by end_of_life desc) rn,
        sum(date_diff('day', start_date, end_of_life)) over(partition by record_id) as TotalDays
    from ads 
    where del_col = true
)
where rn = 1)
where record_id = 146 and TotalDays <= 60 and TotalDays >= 0
group by record_id, hood_id, city_id

赞(0）回复(0）举报 2023-01-04

我来回答

mysql 无法在sql中为我的子查询创建分组依据

1条答案

相关问题

热门标签

最新问答