我自己加入一张桌子并得到以下结果:
+----+----+----+----+----+----+----+----+----+
| id | s1 | s2 | s3 | s4 | s5 | s6 | s7 | s8 |
+----+----+----+----+----+----+----+----+----+
| 1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| 2 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 7 |
| 3 | 10 | 11 | 12 | 11 | 13 | 11 | 8 | 1 |
| 4 | 4 | 2 | 3 | 1 | 10 | 11 | 17 | 13 |
| 5 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
+----+----+----+----+----+----+----+----+----+
如何过滤掉s1到s8中数字不唯一的行,比如第2、3、5行?
基本过滤器将是:
SELECT t1.id, t1.col as s1, t2.col as s2, t3.col as s3, t4.col as s4, t5.col as s5, t6.col as s6, t7.col as s7, t8.col as s8
FROM [...]
WHERE s1 NOT IN (s2, s3, s4, s5, s6, s7, s8),
AND s2 NOT IN (s3, s4, s5, s6, s7, s8),
AND s3 NOT IN (s4, s5, s6, s7, s8),
AND s4 NOT IN (s5, s6, s7, s8),
AND s5 NOT IN (s6, s7, s8),
AND s6 NOT IN (s7, s8),
AND s7 <> s8
我检查每一列是否与其他列不同,这 8 列需要 28 个条件(使用 NOT IN 简化),太多了。
我能以有效的方式做到这一点吗?
更多细节
CREATE TABLE superobject__object (
superobject_id integer NOT NULL,
path smallint NOT NULL,
set smallint NOT NULL,
object_id integer NOT NULL,
color_id integer NOT NULL,
CONSTRAINT superobject_object__pk PRIMARY KEY (superobject_id,path,object_id,color_id)
);
ALTER TABLE superobject__object ADD CONSTRAINT superobject_object__superobject_id_fk FOREIGN KEY (superobject_id) REFERENCES superobject (id);
ALTER TABLE superobject__object ADD CONSTRAINT superobject_object__object_id_fk FOREIGN KEY (object_id) REFERENCES object (id);
ALTER TABLE superobject__object ADD CONSTRAINT superobject_object__color_id_fk FOREIGN KEY (color_id) REFERENCES color (id);
它是组合数据。让它变得简单是相当复杂的。
基本上,超级对象由 1/ 可以具有依赖性 2/ 可以组合在一起作为选项集的对象组成。
想一想您可以购买绿色、黄色或红色(选项集)的汽车。如果您购买红色油漆,您可以花钱选择额外的轮毂盖。
现在,如果您搜索涂有绿色油漆和轮毂盖的汽车,您将找不到那个超级对象,因为轮毂盖有一个依赖项,即红色油漆。
我用上述表格解决了这个问题。
依赖关系(层次结构)根本不深,我可以将超级对象切割成“路径”。
对于我的汽车示例,我有:
superobject_1 path_1 object_green_paint
superobject_1 path_1 object_yellow_paint
superobject_1 path_1 object_common_object
superobject_1 path_2 object_red_paint
superobject_1 path_2 object_hubcaps
superobject_1 path_2 object_common_object
至于选项集,我给路径的每个对象一个不同的整数,并将选项与相同的整数组合在一起:
superobject_1 path_1 set_1 object_green_paint
superobject_1 path_1 set_1 object_yellow_paint
superobject_1 path_1 set_2 object_common_object
superobject_1 path_2 set_1 object_red_paint
superobject_1 path_2 set_2 object_hubcaps
superobject_1 path_2 set_3 object_common_object
为什么我这样做而不是给每个组合自己的路径?因为每增加一组选项,组合就会呈指数级增长。
然后,我搜索:
SELECT s1.superobject_id as id, s1.set, s2.set, s3.set, s4.set, s5.set, s6.set, s7.set, s8.set FROM superobject__object s1
JOIN superobject__object s2 ON (s1.superobject_id, s1.path) = (s2.superobject_id, s2.path)
JOIN superobject__object s3 ON (s1.superobject_id, s1.path) = (s3.superobject_id, s3.path)
...
JOIN superobject__object s8 ON (s1.superobject_id, s1.path) = (s8.superobject_id, s8.path)
WHERE s1.object_id IN (SELECT id FROM object WHERE <filter_1>)
AND s2.object_id IN (SELECT id FROM object WHERE <filter_2>)
AND s3.object_id IN (SELECT id FROM object WHERE <filter_3>)
...
AND s8.object_id IN (SELECT id FROM object WHERE <filter_8>)
这给了我像第一张桌子的东西。然后我继续过滤掉你有来自同一组(选项)的对象的行,因为你无法同时找到带有绿色和黄色油漆的汽车,因为一个选项排除了其他选项。