按user_id选择行，以及共享该条目的event_id的任何行

由环嘉玉发布于 2020-05-18 06:46:56

I have a table which includes the following format (username is added via a JOIN):

 _______________________________
| event_id | user_id | username |
|-------------------------------|
|    30    |    1    |   user1  |
|    30    |    2    |   user2  |
|    30    |    3    |   user3  |
|    31    |    1    |   user1  |
|    31    |    4    |   user4  |
|    31    |    7    |   user5  |
|    32    |    3    |   user1  |
|    32    |    4    |   user4  |
|    32    |    5    |   user5  |
|_______________________________|

我考虑过将用户存储为JSON：

 _______________________________________________________
| event_id |                   users                    |
|-------------------------------------------------------|
|    30    | [{"user_id": 1, "username": "user1"}, ...] |
|    31    | [{"user_id": 1, "username": "user1"}, ...] |
|    32    | [{"user_id": 5, "username": "user5"}, ...] |
|_______________________________________________________|

但是我想当尝试基于JSON值查找事件时，这在性能上会很糟糕。

I want to be able to look up any events where a username appears, and return all rows which have the same event_id. Is this possible to do in a single query (as I wish to minimize server strain), or should I just do a nested SELECT?

gcum

2020-05-18 06:46:56

You could use exists:

select t.*
from mytable t
where exists (
    select 1 from mytable t1 where t1.event_id = t.event_id and t1.username = ?
)

liste

2020-05-18 06:46:56

Try the following using group_concat, here is the db-fiddle.

select
    event_id,
    group_concat(concat('{user_id:"', user_id, '", username:"',username,'"}')) as users
from yourTable
group by
    event_id

输出：

| event_id | users                                                                                           |
| -------- | ----------------------------------------------------------------------------------------------- |
| 30       | {user_id:"1", username:"user1"},{user_id:"2", username:"user2"},{user_id:"3", username:"user3"} |
| 31       | {user_id:"1", username:"user1"},{user_id:"4", username:"user4"},{user_id:"7", username:"user5"} |
| 32       | {user_id:"3", username:"user1"},{user_id:"4", username:"user4"},{user_id:"5", username:"user5"} |

环嘉玉

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3145
注册排名
2018