聊聊mysql的多列组合查询

【聊聊mysql的多列组合查询】本文主要展示如何使用mysql的多列组合查询

何为多列组合查询呢,就是查询的值不再是单个列的值,而是组合列的值。比如where (column1,column2) in ((a1,b1),(a2,b2),(a3,b3))
实例 建表
create table t_demo( id int NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(10), score int ); insert into t_demo(name,score) values('a',10); insert into t_demo(name,score) values('b',20); insert into t_demo(name,score) values('c',30); insert into t_demo(name,score) values('d',40); insert into t_demo(name,score) values('d',50); insert into t_demo(name,score) values('e',60);

多列in查询
select * from t_demo where (name,score) in (('c',30),('e',60)); +----+------+-------+ | id | name | score | +----+------+-------+ | 3| c| 30| | 6| e| 60| +----+------+-------+ 2 rows in set Time: 0.112s

多列=查询
select * from t_demo where (name,score) = ('c',30) or (name,score) = ('e',60); +----+------+-------+ | id | name | score | +----+------+-------+ | 3| c| 30| | 6| e| 60| +----+------+-------+ 2 rows in set Time: 0.119s

小结 多列组合查询平常比较少见,初次看还觉得挺神奇的。
doc
  • mysql-filtering-by-multiple-columns
  • selecting-where-two-columns-are-in-a-set

    推荐阅读