这大致是我一直在尝试做的事情,我似乎无法弄清楚如何使它工作。
SELECT a.name FROM tableA a, TableB b
WHERE a.key = b.key
AND (SELECT b.values FROM tableA a, TableB b
WHERE a.key = b.key
AND a.name = Prime_Key) IN
(SELECT b.values FROM tableA a, TableB b WHERE a.key = b.key)
我有两个表,从tablea到TableB有一对一关系。我需要选择一个从表格中找到所有值的所有值,然后在tablea中找到特定名称,然后找到所有与所有值相关联的名称。因此,最终输出将是具有指向给定prime_key的所有值的名称的列表。
一些示例数据是:
tablea:
name key
Bob 1
Alice 2
Mark 3
Jill 4
Luke 5
和tableb:
key value
1 short
1 boy
2 tall
2 girl
2 blond
3 short
3 brownhair
3 boy
3 golf
4 girl
5 golf
5 boy
5 brownhair
因此,如果我要用 Bob
替换prim_key,结果将是 Mark
,因为他的值较短和男孩。同样,如果prim_key是 Mark
,则不会返回,因为没有其他人拥有他所做的所有值
您可以使用子查询连接 计数匹配来实现您的结果:
SELECT
person_name,
sum(total_match) total_matches,
num_attributes
FROM (
SELECT
search_name,
search_value,
num_attributes,
person_name,
sum(is_match) total_match
FROM (
SELECT
search_subject.name as search_name,
search_subject.value as search_value,
search_subject.total_attributes as num_attributes,
people.name as person_name,
people.value as person_value,
IF(search_subject.value=people.value,1,0) as is_match
FROM (
SELECT
name,
value,
total_attributes
FROM tablea
JOIN tableb USING(`key`)
JOIN (
SELECT count(*) total_attributes
FROM tablea
JOIN tableb using(`key`)
WHERE name = 'Bob'
) attributes_count
WHERE name = 'Bob'
) search_subject
JOIN (
SELECT
name,
value
FROM tablea
JOIN tableb using(`key`)
) people ON ( search_subject.name <> people.name)
) x
GROUP BY search_name, search_value, person_name
) y
GROUP BY person_name
HAVING total_matches = num_attributes
首先,我使用group_concat函数将每个键的组值组为一个,并且在此期间创建了类似子句的模式。
SELECT key,
GROUP_CONCAT(VALUE ORDER BY VALUE DESC SEPARATOR '%' ) AS myValues
FROM tableB
GROUP BY key;
结果是这样的:
key value
1 boy%short
2 blond%girl%tall
3 boy%brownhair%golf%short
4 girl
5 boy%brownhair%golf
然后创建类似查询以获取结果。为简单起见,您可以使用:
创建视图CREATE VIEW table_ab AS (SELECT key, GROUP_CONCAT(VALUE ORDER BY VALUE DESC SEPARATOR '%' ) AS myValues
FROM tableB
GROUP BY key);
最后,您可以通过:
获得结果SELECT
t2.name
FROM
table_ab t1
LEFT JOIN test_a t2
ON t1.key = t2.key
WHERE t1.myvalues LIKE
(SELECT myvalues FROM table_ab WHERE key = (SELECT key FROM test_a a WHERE a.name = 'Bob')) AND t2.name != 'Bob'