mysql从mysql中获取条件值？

So say I have a table called users with the following values: ip, id, and user. If I want to get all the users based on ip, but don't have the ip, then I would do first select ip from users where user = "user"; and afterwards, select user from users where ip = "xxx.xxx.xxx.xx[x]"; (the x's being the ip numbers gotten from the first query). Is there not a way to do something like select user from users where ip = "[insert ip query here to get ip]" where the whole thing is compacted in 1 query? I know this may sound silly over 2 statements, but every cpu cycle counts for my program especially on a rather slow platform.

所以说我有一个名为users的表,其中包含以下值:ip,id和user。如果我想基于ip获取所有用户,但没有ip,那么我会先从用户=“user”的用户中选择ip;然后,从用户中选择用户,其中ip =“xxx.xxx.xxx.xx [x]”; (x是第一个查询得到的ip号)。有没有办法做一些事情,比如从用户中选择用户,其中ip =“[在这里插入ip查询以获取IP]”,其中整个事情在1个查询中被压缩?我知道这听起来可能比2个语句愚蠢,但每个cpu周期都算是我的程序,特别是在一个相当慢的平台上。

3 个解决方案

#1

If I understand correctly a question for the query you're looking for is Find me all users who share the same ip as userX. If it is the case then you can do it either with JOIN

如果我正确理解您正在寻找的查询的问题,请找到与userX共享相同IP的所有用户。如果是这种情况,那么你可以用JOIN来做

SELECT DISTINCT u1.user 
  FROM users u1 JOIN users u2
    ON u1.ip = u2.ip
 WHERE u2.user = 'some_user';

or with a subquery

或者使用子查询

SELECT user 
  FROM users
 WHERE ip IN
 (
     SELECT DISTINCT ip
       FROM users
      WHERE user = 'some_user'
 );

or with EXISTS

或与EXISTS

SELECT DISTINCT u.user 
  FROM users u 
 WHERE EXISTS
 (
     SELECT *
       FROM users
      WHERE ip = u.ip
      AND user = 'some_user'
 );

Note: make sure that you have indices on ip and user.

注意:确保您在ip和user上有索引。

Here is SQLFiddle demo

这是SQLFiddle演示

#2

It looks like you're doing the same query twice, the following example shows a nested select, it it an incredibly redundant example but It shows what you want (I think)

看起来你正在做同样的查询两次,下面的例子显示了一个嵌套的选择,它是一个非常多余的例子,但它显示了你想要的东西(我认为)

SELECT user FROM users 
WHERE user = (SELECT user FROM users 
              WHERE ip = "xxx.xxx.xxx.xx[x]");

In this query the subquery will actually return the same as the outer query so it's pointless.

在此查询中,子查询实际上将返回与外部查询相同的内容,因此它毫无意义。

#3

-1

i guess that u can use subquery like this: select user from USERS where ip in(select ip from USERS where user like "%user%")

我想你可以使用这样的子查询:从US中选择用户所在的ip in(从USERS中选择ip,其中用户喜欢“%user%”)

#1