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

mervyn 2019年4月11日00:32:18MySQL 数据库MySQL一个字段同时满足多个条件的查询语句已关闭评论863

当我们在使用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

weinxin
我的微信公众号
微信扫一扫
mervyn
批量删除Redis key 数据库

批量删除Redis key

项目开发过程中,有时会用到 redis ,很尴尬的时候经常会忘记给 redis key 设置过期时间,这个时候需要我们通过命令行来操作进行删除。需要使用到如下语句: redis-cli -h 127....
从 mysql 用户的角度使用 Elasticsearch 数据库

从 mysql 用户的角度使用 Elasticsearch

本文主要讲述初次接触ES时,如何以使用MySQL的方式来应用 Elasticsearch 从而达到快速入门的目的。 以下例子均基于 Elasticsearch 6.0 例: order_test 表结...