英文:
MySQL on JSON array and subquery issues
问题
你可以使用以下SQL查询来获取与table_b中的ID对应的table_a数据：
select * from table_a where id in (select json_extract(table_a_ids, '$') from table_b where id = 1);
请注意，这个查询假定你想获取table_b中ID为1的记录所关联的table_a数据。
英文:
Q: At present, I have the ID of table_b. How can I query the data corresponding to table_a?
table_a
| id | value |
|---|---|
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
table_b
| id | table_a_ids |
|---|---|
| 1 | [1, 2] |
| 2 | [2, 3] |
It's not effective
select * from table_a where id in (select json_extract(batch_bill_ids, '$') from table_b where id = 1);
答案1
得分: 1
SELECT *
FROM table_a
WHERE EXISTS (
SELECT NULL
FROM table_b
WHERE JSON_CONTAINS(table_b.table_a_ids, CAST(table_a.id AS JSON))
);
英文:
Test this:
SELECT *
FROM table_a
WHERE EXISTS (
SELECT NULL
FROM table_b
WHERE JSON_CONTAINS(table_b.table_a_ids, CAST(table_a.id AS JSON))
);
</details>
# 答案2
**得分**: 1
可以使用 [member of][1] 运算符进行连接，如下所示：
```sql
select a.id, a.value
from table_a a join table_b b
on a.id member of (b.table_a_ids)
where b.id = 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论