mysql查询问题

[cainiaoxxx]

用户数据表A 部分用户
gid user time
10001 张三 2019-05-15 15:36:21
10002 张四 2019-07-16 14:32:32
10003 张物 2019-07-17 13:56:54
10004 张流 2019-07-18 11:06:·12


日志数据表B 包含所有用户

id log time
123455 {"info":[{"score":10,"gid":10002},{"score":-10,"gid":10005}...]} 2019-05-11 08:35:13
123456 {"info":[{"score":10,"gid":10002},{"score":-10,"gid":10005}...]} 2019-05-17 11:34:41
123457 {"info":[{"score":10,"gid":10002},{"score":-10,"gid":10001}...]} 2019-07-17 18:25:91
123458 {"info":[{"score":10,"gid":10003},{"score":-10,"gid":10002}...]} 2019-07-77 11:34:41
123459 {"info":[{"score":10,"gid":10001},{"score":-10,"gid":10003}...]} 2019-08-01 01:58:53


现在要查询 B表 log字段里面任意一个gid存在A表且B.time>A.time的所有记录 ,该如何写sql,线上各位大神帮帮忙,谢谢

[二月十六]

试试这个

SELECT * FROM B JOIN A WHERE B.log LIKE '%'+A.gid+'%' AND B.time>A.time

[cainiaoxxx]

引用 1 楼 二月十六 的回复:

试试这个

SELECT * FROM B JOIN A WHERE B.log LIKE '%'+A.gid+'%' AND B.time>A.time

不行，报错
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+A.gid+'%' AND B.time>A.time' at line 1

[二月十六]

引用 2 楼 cainiaoxxx 的回复:

[quote=引用 1 楼 二月十六 的回复:] 试试这个

SELECT * FROM B JOIN A WHERE B.log LIKE '%'+A.gid+'%' AND B.time>A.time

不行，报错 [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+A.gid+'%' AND B.time>A.time' at line 1 [/quote] 额。mysql拼接不能直接写

SELECT * FROM B JOIN A WHERE B.log LIKE CONCAT('%',A.gid,'%') AND B.time>A.time

[cainiaoxxx]

引用 3 楼 二月十六 的回复:

[quote=引用 2 楼 cainiaoxxx 的回复:]
[quote=引用 1 楼 二月十六 的回复:]
试试这个

SELECT * FROM B JOIN A WHERE B.log LIKE '%'+A.gid+'%' AND B.time>A.time

不行，报错
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+A.gid+'%' AND B.time>A.time' at line 1
[/quote]
额。mysql拼接不能直接写

SELECT * FROM B JOIN A WHERE B.log LIKE CONCAT('%',A.gid,'%') AND B.time>A.time

[/quote]
a.gid 是集合

[二月十六]

引用 4 楼 cainiaoxxx 的回复:

[quote=引用 3 楼 二月十六 的回复:] [quote=引用 2 楼 cainiaoxxx 的回复:] [quote=引用 1 楼 二月十六 的回复:] 试试这个

SELECT * FROM B JOIN A WHERE B.log LIKE '%'+A.gid+'%' AND B.time>A.time

不行，报错 [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+A.gid+'%' AND B.time>A.time' at line 1 [/quote] 额。mysql拼接不能直接写

SELECT * FROM B JOIN A WHERE B.log LIKE CONCAT('%',A.gid,'%') AND B.time>A.time

[/quote] a.gid 是集合[/quote] 什么样的集合，我是看你给的测试数据都是一个一个的id

[cainiaoxxx]

暂且忽略时间，如果单独取一个用户ID的数据是可以的
select * from B where log->'$**.guid' like '%10003%'
如果我要A里面所以用户对于的数据该如何取，怎么把 log->'$**.guid' like '%10003%' 变成可执行的类似 log->'$**.guid' in (select gid from A)

[二月十六]

引用 6 楼 cainiaoxxx 的回复:

暂且忽略时间，如果单独取一个用户ID的数据是可以的 select * from B where log->'$**.guid' like '%10003%' 如果我要A里面所以用户对于的数据该如何取，怎么把 log->'$**.guid' like '%10003%' 变成可执行的类似 log->'$**.guid' in (select gid from A)

两个表关联查询就可以了