我正在努力找出哪些案例与我列表中的至少两个名字有关。所有数据都在同一个表中。我希望我的查询能生成与它们相关的两个以上相同名称的案例列表,以及它们与哪些名称相关。这就是我目前所拥有的:
SELECT related_persons.name
FROM related_persons
WHERE related_persons.case_number =
(SELECT related_persons.case_number
FROM related_persons
WHERE related_persons.name LIKE 'A%'
OR NAME LIKE 'B%'
OR NAME LIKE 'C%'
OR NAME LIKE 'D%'
OR NAME LIKE 'E%'
OR NAME LIKE 'F%');
我收到一个错误,上面写着more than one row returned by a subquery used as an expression
。
我也尝试过以下几种:
SELECT
related_persons.case_number
FROM
related_persons
GROUP by related_persons.case_number
HAVING SUM(CASE WHEN NAME LIKE 'A%' OR NAME LIKE 'B%' OR NAME LIKE 'C%' OR NAME LIKE 'D% OR NAME LIKE 'E%' OR NAME LIKE 'F%' THEN 1 else 0 END) >= 2;
但这给了我只有1个匹配的案例编号。
有什么建议吗?
您可以使用CTE:
WITH Names AS (
SELECT LEFT(name, 1) AS iniLetter, COUNT(*) qty
FROM related_persons
GROUP BY LEFT(name, 1)
HAVING LEFT(name, 1) IN ('B','C','D','E','F')
)
SELECT related_persons.case_number
FROM related_persons
JOIN Names on iniLetter = LEFT(name,1)
我不明白你想要什么,所以我猜。然而,我有理由相信,通过将表连接到自身,在连接中找到不同的名称,可以解决此问题。
这发现了两个名字的起始字母不同,但都以A-F:开头的情况
select distinct t1.case_number
from related_persons t1
join related_persons t2 on t2.case_number = t1.case_number
and left(t2.name, 1) between 'A' and 'F'
and left(t2.name, 1) != left(t1.name, 1)
where left(t1.name, 1) between 'A' and 'F'
如果不需要A-F,而您只想找到两个不同的名称:
select t1.case_number
from related_persons t1
join related_persons t2 on t2.case_number = t1.case_number
and t2.name > t1.name