SQL select from table where key in array group by 列

Question

给定一张 table 账单

# bills
id  | name    | amount  | user_id
1   | jumper  | 100     | 1
2   | gopper  | 200     | 1
3   | jumper  | 150     | 2
4   | blobber | 300     | 3

和一个用户表

# users
id  | name
1   | John Doe
2   | Mike Marley
3   | Bill Mickane

当我执行查询时

select * from bills where user_id in (1,2) order by name, amount desc

我明白了

# bills
id  | name    | amount  | user_id
2   | gopper  | 200     | 1
3   | jumper  | 150     | 2
1   | jumper  | 100     | 1

但我想要的是(以及所有其他列)

# bills
name    | amount
gopper  | 200   
jumper  | 250   

这将如何实现？

我很想用

select * from bills where user_id in (1,2) group by name order by name, amount desc

但不能，因为 group by 还需要列出其他列名，最终不会按需要合并两行。

附注我正在使用 postgresql

Answer 1

如果您只需要这 2 个字段，您可以使用聚合 SUM():

 SELECT 
          name, 
          SUM(amount) 
     FROM 
          bills 
     WHERE 
          user_id IN (1,2) 
     GROUP BY 
          name 
     ORDER BY 
          name, 
          amount

但是，假设您想要的不仅仅是这两个字段...嵌套查询应该可以帮助您。

SELECT
 m.id, m.name, t.userId, t.amount 
FROM Bills m
 INNER JOIN
 (
   SELECT
          user_id as userID,
          SUM(amount) as amount
   FROM
          bills
   GROUP BY user_id
 ) t
ON t.UserID = m.UserID