sqlite—sql查询，用于按类别对2列进行求和，并且每个类别的最大值小于段名称的最大值

jjhzyzn0 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(237)

table是这样的

seg_name    category    revenue
INFREQUENT  Accessoires 15
INFREQUENT  Accessoires 2
INFREQUENT  Make up     9
INFREQUENT  Make up     33
INFREQUENT  Man         8
INFREQUENT  Man         94
INFREQUENT  Women       32
INFREQUENT  Women       23
Loyal       Accessoires 72
Loyal       Accessoires 13
Loyal       Make up     18
Loyal       Make up     62
Loyal       Man         24
Loyal       Man         87
Loyal       Women       92
Loyal       Women       32
Loyal       sun         33
Loyal       sun         42
New         Accessoires 13
New         Accessoires 25
New         Make up     45
New         Make up     42
New         Man         14
New         Man         52
New         Women       32
New         Women       23
New         sun         54
New         sun         31

我想要一个查询，这样我就可以得到这样的表

seg_name    category    revenue
INFREQUENT  Accessoires 17
INFREQUENT  Make up     42
INFREQUENT  Man         102
INFREQUENT  Women       55
Loyal       Accessoires 85
Loyal       Make up     80
Loyal       Man         111
Loyal       Women       124
Loyal       sun         75
New         Accessoires 38
New         Make up     87
New         Man         66
New         Women       55
New         sun         85

在那之后想要最大的收入类别在所有的seg\u名称

seg_name    category    revenue
INFREQUENT  Man         102
Loyal       Women       124
New         Make up     87

sql sqlite

来源：https://stackoverflow.com/questions/62184395/sql-query-to-sum-category-by-category-with-2-column-and-than-max-value-for-each

2条答案

按热度按时间

bvk5enib1#

ue公司 group by 具有窗口功能：

select sc.*
from (select seg_name, category, sum(revenue) as total_revenue,
             row_number() over (partition by seg_name order by sum(revenue) desc) as seqnum
      from t
      group by seg_name, category
     ) sc
where seqnum = 1;

赞(0）回复(0）举报 2021-07-26

rkttyhzu2#

与 MAX() 以及 FIRST_VALUE() 窗口功能：

select distinct seg_name, 
  first_value(category) over (partition by seg_name order by sum(revenue) desc) category,
  max(sum(revenue)) over (partition by seg_name) revenue
from tablename
group by seg_name, category

请看演示。
结果：

| seg_name   | category | revenue |
| ---------- | -------- | ------- |
| INFREQUENT | Man      | 102     |
| Loyal      | Women    | 124     |
| New        | Make up  | 87      |

赞(0）回复(0）举报 2021-07-26

我来回答

sqlite—sql查询，用于按类别对2列进行求和，并且每个类别的最大值小于段名称的最大值

2条答案

相关问题

热门标签

最新问答