SQL查询，什么项目在桶中？

I am trying to solve a problem similar to this one:

我试图解决类似这个问题:

I have a DB table like :

我有一个DB表,如:

 ╔════╦═════════════╦═══════════╦════════╗ 
 ║ ID ║ Bucket Name ║ Item Name ║ action ║ 
 ╠════╬═════════════╬═══════════╬════════╣ 
 ║  1 ║ B_a         ║ item1     ║ insert ║ 
 ║  2 ║ B_a         ║ item2     ║ insert ║ 
 ║  3 ║ B_b         ║ item1     ║ insert ║ 
 ║  4 ║ B_b         ║ item1     ║ remove ║ 
 ║  5 ║ B_a         ║ item1     ║ remove ║ 
 ║  6 ║ B_a         ║ item3     ║ insert ║
 ╚════╩═════════════╩═══════════╩════════╝ 

With an SQL query I want to get the current status of each bucket:

使用SQL查询,我想获取每个存储桶的当前状态:

 ╦═════════════╦═══════════════╦
 ║ Bucket Name ║ items         ║
 ╬═════════════╬═══════════════╬
 ║ B_a         ║ item2, item3  ║
 ║ B_b         ║ NULL          ║
 ╩═════════════╩═══════════════╩

I know I can select each row and and go over the output to find the status of each bucket, but I am looking for a solution using a SQL query. How can I do that?

我知道我可以选择每一行并查看输出以查找每个存储桶的状态,但我正在寻找使用SQL查询的解决方案。我怎样才能做到这一点?

I am currently using MySql, but sql-server is also an option.

我目前正在使用MySql,但sql-server也是一个选项。

1 个解决方案

#1

This is an aggregation query with a few twists. In MySQL, it is not so hard:

这是一个有几个曲折的聚合查询。在MySQL中,它并不那么难:

select bucketname, group_concat(item_name separator ', ') as items
from buckets b
where b.id = (select max(b2.id) from buckets b2 where b2.item_name = b.item_name) and
      b.action <> 'remove'
group by bucketname;