需求
有这么一个需求。根据用户传入的资料匹配出符合这些资料条件的产品。例如：所有产品由这些资料组合而成，银行卡、身份证、户口本、银行流水、房产证、体检证明等等。用户在查询产品时要列出他所能提供的资料，假如用户能提供银行卡、身份证、银行流水。那么他就可以查询出这三种资料任意组合的产品。例如：只需要身份证的产品，需要身份证、银行卡的产品，需要身份证，银行流水的产品。。。思路
1.创建一张资料表用来存放产品资料 表名t_data
CREATE TABLE `t_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_name` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.创建一张资料产品中间表 表名t_data_product
CREATE TABLE `t_data_product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.创建一产品表 表名t_product
CREATE TABLE `t_product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`datas_id` varchar(64) NOT NULL,
`datas_name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
录入一些测试数据如下图所示
测试数据.png
- 查询sql
SELECT t_product.`name` FROM t_product
INNER JOIN
(SELECT temp_b.product_id as productId FROM
(SELECT product_id, count(*) AS data_count FROM t_data_product GROUP BY product_id) temp_a,
(SELECT product_id, count(*) AS after_data_count FROM t_data_product WHERE data_id in(传入条件) GROUP BY product_id) temp_b
WHERE temp_a.product_id=temp_b.product_id and temp_a.data_count=temp_b.after_data_count) temp_c ON t_product.id=temp_c.productId
下面我们来分解这段sql
１.根据传入的查询条件（例如1,2,3,4），查出是查询条件子集的数据并分组（产品表id，根据查询条件匹配出的资料个数）记为temp_a
SELECT product_id, count(*) AS after_data_count FROM t_data_product WHERE data_id in(1,2,3,4) GROUP BY product_id
查询结果
sql1.png
２.对中间表进行分组得到分组后的数据（产品表id，业务需要资料个数）记为temp_b
SELECT product_id, count(*) AS data_count FROM t_data_product GROUP BY product_id
查询结果
sql2.png
３.匹配出temp_a 和 temp_b 中 product_id 和 资料个数相等的数据 拿到过滤后的 product_id 记为temp_c
SELECT temp_b.product_id as productId FROM
(SELECT product_id, count(*) AS data_count FROM t_data_product GROUP BY product_id) temp_a,
(SELECT product_id, count(*) AS after_data_count FROM t_data_product WHERE data_id in(1,2,3,4) GROUP BY product_id) temp_b
WHERE temp_a.product_id=temp_b.product_id and temp_a.data_count=temp_b.after_data_count
查询结果
sql3.png
4.至此就查询出了符合条件的产品信息
SELECT t_product.`name` FROM t_product
INNER JOIN
(SELECT temp_b.product_id as productId FROM
(SELECT product_id, count(*) AS data_count FROM t_data_product GROUP BY product_id) temp_a,
(SELECT product_id, count(*) AS after_data_count FROM t_data_product WHERE data_id in(1,2,3,4) GROUP BY product_id) temp_b
WHERE temp_a.product_id=temp_b.product_id and temp_a.data_count=temp_b.after_data_count) temp_c ON t_product.id=temp_c.productId
- 测试结果
例如传入条件是资料1,2,3,4 根据需求应该查出产品a,b,c
传入条件1,2,3,4.png
- 后记
我对数据库的表一直是这样理解的。一般的，我会把一张数据表的所有数据当成数学意义上的一个全集，然后根据查询条件，拿出属于这个全集的子集数据。那么这个需求恰好就是把数据当成了各个子集，把我们传入的条件作为一个全集，查询出属于这个全集的所有子集。
