使用单个语句从同一表中的不同行中选择数据

I'm selecting data from two different rows in the same table using a single sql.

我使用一个sql从同一个表中的两个不同的行中选择数据。

"id"    "borrowMax" "holder"    "category"  "country"
"1"     "2"         "0"         "3"         "US"
"2"     "0"         "1"         "10"        "US"

What I'm trying to do works out to this.

我要做的就是这个。

select id, holder from mytable where id = 2
select borrowMax from mytable where id = (
        holder from the above select, in this case it's 1
) and category = 3

The way I do it after looking at examples online is

我在网上看完例子后做的方法是

SELECT col1.id, col1.holder, col2.borrowMax
FROM collection_db col1
JOIN collection_db col2
ON col2.holder = col1.id
WHERE col1.id = 2 //Here, 2 is the value supplied by me
AND col2.category = 3

Sure, this works. But since it's something I pieced together myself, I have my doubts. How would you do something like this? Am I on the right track? (I'm sure I'm not).

当然,这个工作。但既然这是我自己拼凑起来的东西，我有我的怀疑。你怎么能做这样的事?我走对了吗?(我敢肯定,我不是)。

2 个解决方案

#1

I would make use of nested select statements for a use case like yours. There is no JOIN operation being used, just a select query over a already filtered set of results and a logically more coherent code.

我将为您这样的用例使用嵌套select语句。没有使用JOIN操作，只是对已经筛选过的结果集和逻辑上更一致的代码进行选择查询。

SELECT borrowmax, holder, id FROM mytable WHERE 
  id = (SELECT holder FROM mytable WHERE id = 2 )
    AND category = 3

#2

You can also use table alias for this.

您还可以为此使用表别名。

select t1.id, t1.holder, t2.borrowMax from mytable t1, mytable t2 where t1.id = t2.holder