当前访客身份:游客 [ 登录  | 注册加入尚学堂]
直播

Queen

拥有积分:3
这家伙太懒,还没有签名!

博客分类

笔记中心

课题中心

提问中心

答题中心

解答题中心

记一次在数据库中查询:“包含”或者“仅包含”某些商品的订单的方法

Queen 发表于 1月前 (2016-12-30 14:18:33)  |  评论(0)  |  阅读次数(36)| 0 人收藏此文章,   我要收藏   

有这样一个需求:

  1. 从数据库中查出包含“商品1”和“商品2”的订单;
  2. 从数据库中查出包含“商品1”或“商品2”的订单;
  3. 从数据库中查出仅包含“商品1”和“商品2”的订单;
  4. 从数据库中查出仅包含“商品1”或“商品2”的订单;

这里只用“商品1”、“商品2”举例,可以扩展到多个商品的需求。

涉及到的表大概如下图:

该怎么做呢?以第一点需求为例,一种可行的方法是:先查出所有包含“商品1”的订单,然后遍历这些订单,选出包含“商品2”的订单,如果要查询包含更多商品的订单,需要进行多次遍历,层层筛选,效率低下。况且,在实际情况下,往往需要支持分页查询,这种方式基本不可行,或者实现起来很复杂。

第二种方法是这样的:假设查询包含“商品1”、“商品2”、“商品3”的订单,其goods_id 分别为1、2、3,sql如下:

SELECT * FROM `order` 
WHERE order_id IN ( 
SELECT tmp.order_id FROM ( SELECT order_id FROM order_item WHERE goods_id = 1 ) AS tmp 
INNER JOIN ( SELECT order_id FROM order_item WHERE goods_id = 2 ) AS t2 ON tmp.order_id = t2.order_id 
INNER JOIN ( SELECT order_id FROM order_item WHERE goods_id = 3 ) AS t3 ON tmp.order_id = t3.order_id 
)
LIMIT 0,10

如果要求仅包含的话,可以这样写:

SELECT * FROM `order` 
WHERE order_id IN ( 
SELECT tmp.order_id FROM ( SELECT order_id FROM order_item WHERE goods_id = 1 ) AS tmp 
INNER JOIN ( SELECT order_id FROM order_item WHERE goods_id = 2 ) AS t2 ON tmp.order_id = t2.order_id 
INNER JOIN ( SELECT order_id FROM order_item WHERE goods_id = 3 ) AS t3 ON tmp.order_id = t3.order_id 
WHERE (SELECT count(*) from order_item WHERE order_id = tmp.order_id) = 3
)
LIMIT 0,10

增加where条件,限制该订单只有三个商品即可。

以上说明的需求点1和3的实现方式,需求点2和4可以用类似的方式实现。

这种实现方式确实能够满足需求,但是有一个比较严重的问题:当关联的商品多了以后,多个inner join的使用,会使查询效率非常低,尤其是订单量大的时候,会更慢,如果再加上其他查询条件,如:下单时间、发货时间、订单状态等等各类条件以后,(此处已无法描述)。亲测查询7到8个商品,几十万订单的时候,已经慢到不要不要的了。

最后一种实现方式,也即本文的重点,这种方式需要对数据结构做一小小改动,如下图:

如图所示,goods表和order表都增加了一个字段:bit。

先说goods表中的bit,该字段表示对商品编码,取值为2的n次方(n>=0),假设goods表有5个商品,那么bit值依次为1、2、4、8、16、32.

再说order表中的bit,该字段表示该订单所包含商品的bit值之和,假设某订单包含bit值为1和2的商品,那么其bit为3.

如何实现需求呢?先说需求1,假设“商品1”的bit为1,“商品2”的bit为2,那么查询包含“商品1”和“商品2”的订单的sql如下:

SELECT * from `order` where bit & 3 = 3

这里的3为“商品1”和“商品2”bit之和。这个要怎么理解呢,从goods的bit说起,商品的bit用二进制表示如下图:

通过上图,可以想象order表中的bit的值的二进制形式,如果订单包含商品1、2,则二进制位11;如果包含商品1、3,则二进制位101;如果包含商品1、2、3,则二进制位111,。。。商品的各种组合,其bit值相加不会产生进位,因为每个商品bit值的“1”位对应其他商品bit值都为“0”位,所以商品的各种组合对应的订单的bit值唯一,这也是为何商品的bit要求取值为2的n次方(n>=0)。

因此,上述sql中,如果order的bit 和 所要查询商品的bit之和按位与运算,如果结果为查询商品的bit之和,那么说明order的bit相应位为1,订单中有该商品。

需求2,包含“商品1”或“商品2”怎么表示?sql如下:

SELECT * from `order` where bit & 3 > 0 

意思是说,订单bit的相应位至少有一个商品的bit即可。

需求3:仅包含“商品1”和“商品2”怎么表示?sql如下:

SELECT * from `order` where bit = 3

需求4:仅包含“商品1”或“商品2”怎么表示?这种情况下要求订单中只有一个商品,为“商品1”或“商品2”。sql如下:

SELECT * from `order` where bit & (bit - 1) =0 and bit & 3 != 0

这里 bit & (bit -1) = 0 保证订单中只有一个商品,bit & 3  != 0 保证订单中的商品为“商品1”或者“商品2”。

这种方法效率很高,满足需求的同时也简化了sql。

 

分享到:0
关注微信,跟着我们扩展技术视野。每天推送IT新技术文章,每周聚焦一门新技术。微信二维码如下:
微信公众账号:尚学堂(微信号:bjsxt-java)
北京总部地址:北京市海淀区西三旗桥东建材城西路85号神州科技园B座三层尚学堂 咨询电话:400-009-1906 010-56233821
Copyright 2007-2015 北京尚学堂科技有限公司 京ICP备13018289号-1 京公网安备11010802015183