Question

mysql 怎么查询一个字段对应的多个值

Answer 1

in(v1,v2..vn) ，符合v1,v2,,,vn才能被查出
IN关键字可以判断某个字段的值是否在指定的集合中。如果字段的值在集合中，则满足查询条件，该纪录将被查询出来。如果不在集合中，则不满足查询条件。其语法规则如下：[ NOT ] IN ( 元素1, 元素2, …, 元素n )

例如：

select * from STUDENT where STU_AGE in(11,12);

Answer 2

请参见下列mysql实验：
--
生成orderproduct表
create
table
orderproduct(orderid
char(11)
primary
key,productid
varchar(255));
--
product表
create
table
product(productid
varchar(10)
primary
key,productname
varchar(50));
--
向订单表插入数据
insert
into
orderproduct
values
(20161116001,'d0020'),
(20161116035,'e0055'),
(20161101048,'a0035'),
(20161005321,'b0049'),
(20160901515,'c0038'),
(20160814525,'c0038,a0035,e0055'),
(20160714510,'d0020,b0049');
--
向产品表插入数据
insert
into
product
values
('d0020','立顿牌绿茶'),
('e0055','越南小面包'),
('a0035','珠宝台历'),
('b0049','护手霜'),
('c0038','运动水壶');
select
*
from
orderproduct;
select
*
from
product;
--
返回第一问的sql语句
select
a.orderid,min(a.productid)
as
productid,
group_concat(b.productname)
as
productname
from
orderproduct
a,product
b
where
b.productname
in
('运动水壶','珠宝台历','越南小面包')
and
instr(a.productid,b.productid)>0
group
by
a.orderid;
--
返回第二问的sql语句
select
a.orderid,min(a.productid)
as
productid,
group_concat(b.productname)
as
productname
from
orderproduct
a,product
b
where
b.productname
in
('立顿牌绿茶','护手霜')
and
instr(a.productid,b.productid)>0
group
by
a.orderid;