SQL - 将值传递给同一个表上的嵌套选择查询而不选择值

So, I need to extract some metadata from a table which stores the keys and values, so I first choose a specific key to make a SELECT query, (key_a), then I make another query keeping the id and aiming at another data type (key_b), the following is a simplification of the code I have:

所以，我需要从存储键和值的表中提取一些元数据，所以我首先选择一个特定的键来进行SELECT查询（key_a），然后我创建另一个查询来保持id并瞄准另一种数据类型（ key_b），以下是我所拥有的代码的简化：

SELECT
    id AS param,
    val as key_a,
    (
        SELECT val
        FROM table
        WHERE key = 'key_b' AND id = param
    ) AS key_b

FROM table

WHERE val = 'key_a'

What I want is to achieve this without the SELECT query selecting the id field. Trying to remove the id AS param, line and replacing param with id or table.id have not worked so far. I am kinda lost in here since it's the first time I am working with nested queries and can't find anything regarding same table nested queries.

我想要的是在没有SELECT查询选择id字段的情况下实现这一点。试图删除id AS param，line和用id或table.id替换param到目前为止还没有工作。我有点迷失在这里，因为这是我第一次使用嵌套查询而无法找到有关相同表嵌套查询的任何内容。

Am I doing something wrong?

难道我做错了什么？

3 个解决方案

#1

So your problem is, that you have to compare the column id with itself, right? Then just alias the table name instead of the column.

所以你的问题是，你必须将列id与自身进行比较，对吗？然后只是别名表名而不是列。

SELECT
    val as key_a,
    (
        SELECT val
        FROM table b
        WHERE key = 'key_b' AND id = a.id
    ) AS key_b
FROM table a
WHERE val = 'key_a'

#2

Use a join. If the tables are the same table, use a self-join.

使用联接。如果表是同一个表，请使用自联接。

SELECT
   a.val as key_a,
   b.val AS key_b
FROM table a
LEFT JOIN table b ON b.id = a.id AND b.key = 'key_b'
WHERE a.val = 'key_a'  -- perhaps you meant a.key here?
;

#3

SELECT DISTINCT A.id as param,
  (SELECT B.val from table B where B.id = A.id and B.key = 'key_a'),
  (SELECT C.val from table C where C.id = A.id and C.key = 'key_b') 
from table A

I'm not sure what you want , but hope this solve your issue

我不确定你想要什么，但希望这能解决你的问题

#1