带有双选择语句的嵌套 mysql 查询? [英] nested mysql query with double select statement?

问题描述

我有一个关于嵌套查询的简单问题.老实说，我不知道是否只能通过一个查询来完成，或者我是否必须使用 PHP.

I have a simple problem regarding a nested query. To be honest I don't know if it can be done through one query only or if I'll have to use PHP.

简单地说，我想通过从关系表中的选择语句返回的用户 ID 返回用户表中的用户信息.

Put simply, I want to return the users information from the users tables by the users IDs returned from a select statement in relations table.

我可以通过 2 个查询和一些 PHP 循环来做到这一点，但为了节省资源，但我认为最好将其合并为 1 个查询和单个循环.

I can do this by 2 queries and some PHP loop but for saving resources, but I think it's better to combine it into 1 query and single loop.

第一次查询

SELECT UserID FROM relations WHERE GroupID = '1'

第二个查询，我需要通过第一个 select 语句返回的 UsersID 从用户表中检索用户信息.

Second query I need to retrieve the user info from the user table by the returned UsersIDs from the first select statement.

我可以通过循环遍历 ID 并进行查询来完成此操作，但我认为我可以在 1 个查询中获得全部.

I can do this by loop through the ID and making the queries but I think I can get all in 1 query.

谢谢

推荐答案

这是典型的做法:

SELECT     users.*
FROM       users
INNER JOIN relations
ON         users.id = relations.userid
WHERE      relations.groupid = 1

我注意到您在查询中的 1 周围使用了引号.我假设 group id 是一个整数值，在这种情况下你不应该使用引号.

I noticed you were using quotes around the 1 in your query. I am assuming group id is an integer value, in which case you should not use quotes.

使用 IN 子查询的答案的性能可能较差.特别是对于 MySQL，JOIN 应该始终是我们从表中组合结果的首选方式，因为 MySQL 的子查询实现特别乏味.

The answers that use an IN subquery are likely to be less performant. Esp. with MySQL, the JOIN should always we the preferred way to combine results from tables, since MySQL has particularly lackluster subquery implementation.

这篇关于带有双选择语句的嵌套 mysql 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！