sqlite - SQLite 3 CROSS或INTERSECT复杂子查询

Question

我有3个相关表格。一个表包含我实际要查找的行，另一个表包含我需要搜索的数据，第三个表描述了我要查找的数据。我从以下查询中得到不想要的结果：

SELECT * FROM names WHERE namesKey IN ( SELECT namesKey FROM data WHERE 
    ( dataType IS 3 AND data IS 'COINCIDENCE' ) 
    AND ( dataType IS 2 AND data IS 'STATE' ) 
    AND ( dataType IS 1 AND data IS 'COUNTRY' ) );

我需要基于过滤器表中的多行进行查询的帮助。我需要与第二个表中存在于多个行中的键相对应的行...我在解释很糟糕...这是一个示例：

DROP TABLE IF EXISTS names ;
CREATE TABLE names (
    namesKey INTEGER PRIMARY KEY ASC,
    name TEXT NOT NULL
);
DROP TABLE IF EXISTS data ;
CREATE TABLE data (
    dataKey INTEGER PRIMARY KEY ASC,
    namesKey INTEGER NOT NULL,
    dataType INTEGER NOT NULL,
    data TEXT NOT NULL,
    FOREIGN KEY(namesKey) REFERENCES names(namesKey)
);
DROP TABLE IF EXISTS filter ;
CREATE TABLE filter (
    filterKey INTEGER PRIMARY KEY ASC,
    dataType INTEGER NOT NULL,
    data TEXT NOT NULL
);

INSERT INTO names( name ) VALUES ( 'name1' );
INSERT INTO names( name ) VALUES ( 'name2' );
INSERT INTO names( name ) VALUES ( 'name3' );
INSERT INTO names( name ) VALUES ( 'name4' );
INSERT INTO names( name ) VALUES ( 'name5' );
INSERT INTO names( name ) VALUES ( 'name6' );
INSERT INTO names( name ) VALUES ( 'name7' );
INSERT INTO names( name ) VALUES ( 'name8' );
INSERT INTO names( name ) VALUES ( 'name9' );

INSERT INTO data( namesKey, dataType, data ) VALUES ( 1, 1, 'COUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 1, 2, 'STATE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 1, 3, 'CITY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 2, 1, 'COUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 2, 2, 'STATE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 2, 3, 'OTHERCITY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 3, 1, 'COUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 3, 2, 'STATE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 3, 3, 'COINCIDENCE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 4, 1, 'COUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 4, 2, 'OTHERSTATE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 4, 3, 'COINCIDENCE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 5, 1, 'OTHERCOUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 5, 2, 'RANDOM' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 5, 3, 'COINCIDENCE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 6, 1, 'OTHERCOUNTRY' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 6, 2, 'OTHERSTATE' );
INSERT INTO data( namesKey, dataType, data ) VALUES ( 6, 3, 'COINCIDENCE' );

INSERT INTO filter( dataType, data ) VALUES ( 1, 'COUNTRY' );
INSERT INTO filter( dataType, data ) VALUES ( 2, 'STATE' );
INSERT INTO filter( dataType, data ) VALUES ( 3, 'COINCIDENCE' );

现在，我需要的是能够相对可靠地运行3种不同类型的查询。


我需要搜索“无数据”并获得名称7、8和9


这很容易：

SELECT * FROM names WHERE namesKey NOT IN ( SELECT namesKey FROM data ) ;

我需要根据数据表中的单一数据类型进行搜索


同样容易，所需的结果3、4、5和6

SELECT * FROM names WHERE 
    namesKey IN ( SELECT namesKey FROM data WHERE 
        ( dataType IS 3 AND data IS 'COINCIDENCE' ) ) 
;

我需要基于来自过滤器表的多行进行搜索。我不知道该怎么办...


所需结果仅是name3行
我可以做到

SELECT * FROM names WHERE 
    namesKey IN ( SELECT namesKey FROM data WHERE 
        ( dataType IS 3 AND data IS 'COINCIDENCE' ) )
    AND 
    namesKey IN ( SELECT namesKey FROM data WHERE 
        ( dataType IS 2 AND data IS 'STATE' ) )
    AND 
    namesKey IN ( SELECT namesKey FROM data WHERE 
        ( dataType IS 1 AND data IS 'COUNTRY' ) )
;

但这就是丑陋的大写字母UGH！



甚至使用这种方法更糟的是，dataType在理论上是任意大的，因此我可能最终试图将数十个甚至数百个子查询串在一起……我可能会用完RAM甚至在尝试将其放入之前就只编写我的字符串SQL。
因此，我正在寻找更优雅的解决方案。有什么建议？

Answer 1

您可以将过滤器表直接与实际表连接以获取具有匹配项的行，然后仅检查所有三个搜索词都匹配的那些名称键，即，匹配行数与所有搜索数相同的组值：

SELECT namesKey
FROM data
JOIN filter USING (dataType, data)
GROUP BY namesKey
HAVING COUNT(*) = (SELECT COUNT(*) FROM filter);

然后照常使用这些名称键：

SELECT * 
FROM names 
WHERE namesKey IN (SELECT namesKey...);