如何简化这个 SQL 语句？

发布于 2024-11-07 12:18:43 字数 539 浏览 0 评论 0原文

我有两个表，首先，我需要进行一些搜索以获取另一个表的 fk，如下所示...

SELECT `related_table_id` 
FROM `table_a` 
WHERE `users_id` = '14' 
  AND `user_data_status` = 'n' 
  AND `another_table_name` = 'table_b'

然后，我有许多 related_table_id 作为输出...并且我需要搜索内容，就像 %keyword% 。所以，我需要像这样查询另一个SQL：

SELECT * 
FROM table_b 
WHERE (`table_b_id`='218' OR  `table_b_id`='219' OR  `table_b_id`='225') 
  AND `content` LIKE '%keyword%'

问题是，当我的数据库增长时，table_b可能有很多可以从table_a查询的数据，所以，sql语句会变得非常非常长。我可以将这两个语句合并为一个吗？谢谢/

原文

I have two table, first, I need do some searching to get the fk of another table like this...

SELECT `related_table_id` 
FROM `table_a` 
WHERE `users_id` = '14' 
  AND `user_data_status` = 'n' 
  AND `another_table_name` = 'table_b'

then, I have many related_table_id as an output... and I need to search the content, which is like %keyword%.
So, I need to query another SQL like this:

SELECT * 
FROM table_b 
WHERE (`table_b_id`='218' OR  `table_b_id`='219' OR  `table_b_id`='225') 
  AND `content` LIKE '%keyword%'

The question is, when my db grown, and the table_b, may have a lot of data that can query from table_a, so, the sql statement will become very very long. Can I combine these two statement in one? Thank you/

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

紫竹語嫣☆ 2024-11-14 12:18:43

您可以使用 INNER JOIN 来“匹配“两个或多个表的行。

SELECT table_b.* 
FROM table_b 
INNER JOIN table_a
ON table_a.related_table_id = table_b.table_b_id
WHERE table_a.users_id = '14' 
  AND table_a.user_data_status = 'n' 
  AND table_a.another_table_name = 'table_b'
  AND table_b.content LIKE '%keyword%'

You can use INNER JOIN to "match" the rows of two or more tables.

SELECT table_b.* 
FROM table_b 
INNER JOIN table_a
ON table_a.related_table_id = table_b.table_b_id
WHERE table_a.users_id = '14' 
  AND table_a.user_data_status = 'n' 
  AND table_a.another_table_name = 'table_b'
  AND table_b.content LIKE '%keyword%'

回复收藏 0 原文

倾城花音 2024-11-14 12:18:43

执行嵌套查询：

SELECT * FROM table_b WHERE `table_b_id` IN (SELECT `related_table_id` FROM `table_a` WHERE `users_id` = '14' AND `user_data_status` = 'n' AND `another_table_name` = 'table_b') AND `content` LIKE '%keyword%'

Do a nested query:

SELECT * FROM table_b WHERE `table_b_id` IN (SELECT `related_table_id` FROM `table_a` WHERE `users_id` = '14' AND `user_data_status` = 'n' AND `another_table_name` = 'table_b') AND `content` LIKE '%keyword%'

回复收藏 0 原文

~没有更多了~