MySQL一个字段同时满足多个条件的查询语句

当我们在使用mysql的时候经常会一对多的关系进行建表。而在实际使用的过程中经常会有一些需要查询子表的某个字段同时满足多个条件的数据。本文就是基于此类场景来介绍如何通过sql语句实现该业务需求。文章源自编程技术分享-https://mervyn.life/3d53e25a.html

以订单信息为例，会建立一个订单基础表 order 及订单对应的商品信息表 order_goods 。我们需要查找同时购买了某几个商品的订单信息。文章源自编程技术分享-https://mervyn.life/3d53e25a.html

表结构如下：文章源自编程技术分享-https://mervyn.life/3d53e25a.html

CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` char(16) NOT NULL COMMENT '订单号',
  `pay_success_time` int(11) NOT NULL DEFAULT '0' COMMENT '成功支付的时间',
  `uid` int(11) NOT NULL DEFAULT '0' COMMENT '购买人',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单状态 0 未支付 1 支付成功 2 支付失败 3 取消支付',
  `price` decimal(4) NOT NULL DEFAULT '0' COMMENT '订单金额',
  `created_at` int(11) NOT NULL DEFAULT '0' COMMENT '订单创建时间',
  `updated_at` int(11) NOT NULL DEFAULT '0' COMMENT '订单最后更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_order_no` (`order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_order_goods` (
  `id` int(11) NOT NULL,
  `order_no` char(32) NOT NULL COMMENT '订单号',
  `goods_id` int(11) NOT NULL DEFAULT '0' COMMENT '成功支付的时间',
  `created_at` int(11) NOT NULL DEFAULT '0' COMMENT '订单创建时间',
  `updated_at` int(11) NOT NULL DEFAULT '0' COMMENT '订单最后更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_order_no` (`order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

本文主要是为了讲解如何构造查询语句，所以请忽略表结构的合理性问题。文章源自编程技术分享-https://mervyn.life/3d53e25a.html

INSERT INTO t_order VALUES(1, 'o201904101524111', unix_timestamp(now()), 100001, 1, 15, unix_timestamp(now()), unix_timestamp(now()));
INSERT INTO t_order VALUES(2, 'o201904101524112', unix_timestamp(now()), 100001, 1, 15, unix_timestamp(now()), unix_timestamp(now()));
INSERT INTO t_order VALUES(3, 'o201904101524113', unix_timestamp(now()), 100001, 1, 15, unix_timestamp(now()), unix_timestamp(now()));

INSERT INTO t_order_goods VALUES(1, 'o201904101524111', 1000, unix_timestamp(now()), unix_timestamp(now()));
INSERT INTO t_order_goods VALUES(2, 'o201904101524111', 1001, unix_timestamp(now()), unix_timestamp(now()));
INSERT INTO t_order_goods VALUES(3, 'o201904101524111', 1002, unix_timestamp(now()), unix_timestamp(now()));

INSERT INTO t_order_goods VALUES(4, 'o201904101524112', 1001, unix_timestamp(now()), unix_timestamp(now()));
INSERT INTO t_order_goods VALUES(5, 'o201904101524112', 1002, unix_timestamp(now()), unix_timestamp(now()));
INSERT INTO t_order_goods VALUES(6, 'o201904101524112', 1003, unix_timestamp(now()), unix_timestamp(now()));

INSERT INTO t_order_goods VALUES(7, 'o201904101524113', 1000, unix_timestamp(now()), unix_timestamp(now()));
INSERT INTO t_order_goods VALUES(8, 'o201904101524113', 1004, unix_timestamp(now()), unix_timestamp(now()));
INSERT INTO t_order_goods VALUES(9, 'o201904101524113', 1003, unix_timestamp(now()), unix_timestamp(now()));

查找同时购买了商品1000及1002的订单总数文章源自编程技术分享-https://mervyn.life/3d53e25a.html

select count(*) as num from (
    select o.order_no from t_order as o
        JOIN t_order_goods as g on o.order_no = g.order_no
        where g.goods_id in(1000, 1002)
        group by o.order_no
        having count(*) >= 2
) as o

注意： 这里如果 order_goods 表对应的 goods_id 会出现重复的话上述查询会导致查询出来的结果有误。文章源自编程技术分享-https://mervyn.life/3d53e25a.html