mysql 行行比较

CREATE TABLE `t_ware_sale_statistics` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `business_id` bigint(20) NOT NULL COMMENT '业务机构编码',
  `ware_inside_code` bigint(20) NOT NULL COMMENT '商品自编码',
  `create_user` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_user` bigint(20) DEFAULT NULL COMMENT '最终修改人',
  `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最终修改时间',
  `is_delete` tinyint(2) DEFAULT '2' COMMENT '是否删除，1：是，2：否',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_business_ware` (`business_id`,`ware_inside_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='商品销售统计';

INSERT INTO `demo`.`t_ware_sale_statistics` (`id`, `business_id`, `ware_inside_code`, `create_user`, `create_time`, `modify_user`, `modify_time`, `is_delete`) VALUES ('1', '1', '10', NULL, '2021-08-25 14:49:14', NULL, '2021-08-25 14:54:59', '2');
INSERT INTO `demo`.`t_ware_sale_statistics` (`id`, `business_id`, `ware_inside_code`, `create_user`, `create_time`, `modify_user`, `modify_time`, `is_delete`) VALUES ('2', '1', '20', NULL, '2021-08-25 14:49:33', NULL, '2021-08-25 14:54:59', '2');
INSERT INTO `demo`.`t_ware_sale_statistics` (`id`, `business_id`, `ware_inside_code`, `create_user`, `create_time`, `modify_user`, `modify_time`, `is_delete`) VALUES ('3', '1', '30', NULL, '2021-08-25 14:49:38', NULL, '2021-08-25 14:55:00', '2');
INSERT INTO `demo`.`t_ware_sale_statistics` (`id`, `business_id`, `ware_inside_code`, `create_user`, `create_time`, `modify_user`, `modify_time`, `is_delete`) VALUES ('4', '2', '10', NULL, '2021-08-25 14:49:42', NULL, '2021-08-25 14:55:01', '2');
INSERT INTO `demo`.`t_ware_sale_statistics` (`id`, `business_id`, `ware_inside_code`, `create_user`, `create_time`, `modify_user`, `modify_time`, `is_delete`) VALUES ('5', '2', '20', NULL, '2021-08-25 14:49:53', NULL, '2021-08-25 14:55:01', '2');
INSERT INTO `demo`.`t_ware_sale_statistics` (`id`, `business_id`, `ware_inside_code`, `create_user`, `create_time`, `modify_user`, `modify_time`, `is_delete`) VALUES ('6', '3', '10', NULL, '2021-08-25 14:49:57', NULL, '2021-08-25 14:55:02', '2');
INSERT INTO `demo`.`t_ware_sale_statistics` (`id`, `business_id`, `ware_inside_code`, `create_user`, `create_time`, `modify_user`, `modify_time`, `is_delete`) VALUES ('7', '3', '40', NULL, '2021-08-25 14:50:06', NULL, '2021-08-25 14:55:03', '2');

 

 

问题就是：如何查询业务机构1下，10,20商品 和 业务机构2下，10,30商品

 

方式一:union all

-- explain
SELECT * FROM `t_ware_sale_statistics`
where business_id = 1 and ware_inside_code in (10,20)
union all
SELECT * FROM `t_ware_sale_statistics`
where business_id = 2 and ware_inside_code in (10,30)

 

方式二:or拼接

-- explain
SELECT * FROM `t_ware_sale_statistics`
where (business_id = 1 and ware_inside_code in (10,20))
or (business_id = 2 and ware_inside_code in (10,30))

 

方式三:混查过滤

-- explain
SELECT * FROM `t_ware_sale_statistics`
where business_id in (1,2) and ware_inside_code in (10,20,30)

但是：查出来的结果集大于等于我们想要的结果集，所以还需要对查出来的结果集进行一次过滤，过滤出我们想要的结果集

 

方式四:行行比较

SQL-92 中加入了行与行比较的功能，这样一来，比较谓词 = 、< 、> 和 IN 谓词的参数就不再只是标量值了，还可以是值列表了

-- explain
SELECT * FROM `t_ware_sale_statistics`
where (business_id,ware_inside_code)
in ((1,10),(1,20),(2,10),(2,30));