按特定列获取与搜索行相关的行

Question

我正在尝试实现以下场景的sql查询，

td style="text-align:left；">B124>td style="ext-align:center；">Darln>td style="text-align:right；">125td style="text-align:left；">B123/table>

user_id	nic_number	reg_number	full_name
B123	1234	12346		B125	12347	1214
B123	1234	1234

您可以将nic和reg号组合在一个唯一的密钥中以获得您的记录。

EDITED只提取相关行，而不提取代码搜索的行，顺便说一下，代码在subscribers表中似乎不是唯一的。

select 
code,
trim(nic_number) as nic_number,
trim(reg_number) as reg_number,
trim(full_name) as full_name,
trim(code) as code 
from 
subscribers s1
where 
code <> lower(trim('123')) 
and trim(nic_number) + '|' + trim(reg_number) IN (
select trim(nic_number) + '|' + trim(reg_number)
from subscribers
where code = lower(trim('123'))
)

我不确定您为什么指定了"无连接"-我知道你可能不想在表上有触发器(无论如何你都不需要实现这一点(，但JOIN是标准的SQL语法，它将帮助你实现你想要的结果。

尝试：

SELECT
s1.code, s1.nic_number, s1.reg_number, s1.full_name
FROM subscribers s1
INNER JOIN
(
SELECT nic_number, reg_number
FROM subscribers
WHERE code = '123'
) s2
ON s1.nic_number = s2.nic_number
AND s1.reg_number = s2.reg_number
WHERE s1.code <> '123';

或者，如果你真的需要在根本没有JOIN的情况下实现它，那么你只需要加倍你需要包括的子查询：

SELECT
s1.code, s1.nic_number, s1.reg_number, s1.full_name
FROM subscribers s1
WHERE s1.nic_number IN
(
SELECT nic_number FROM subscribers
WHERE code = '123'
)
AND s1.reg_number IN
(
SELECT reg_number FROM subscribers
WHERE code = '123'
)
AND s1.code <> '123';

后一种查询不一定是理想的，但它仍然达到了期望的结果。