查询出需要资料等于或小于用户提供资料的产品

• 需求
  　　有这么一个需求。根据用户传入的资料匹配出符合这些资料条件的产品。例如：所有产品由这些资料组合而成，银行卡、身份证、户口本、银行流水、房产证、体检证明等等。用户在查询产品时要列出他所能提供的资料，假如用户能提供银行卡、身份证、银行流水。那么他就可以查询出这三种资料任意组合的产品。例如：只需要身份证的产品，需要身份证、银行卡的产品，需要身份证，银行流水的产品。。。

• 思路
  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

• 后记
  　　我对数据库的表一直是这样理解的。一般的，我会把一张数据表的所有数据当成数学意义上的一个全集，然后根据查询条件，拿出属于这个全集的子集数据。那么这个需求恰好就是把数据当成了各个子集，把我们传入的条件作为一个全集，查询出属于这个全集的所有子集。