MySQL查询问题，MYSQL连表查询可以一一对应查到对应的字段，如果有多个字段怎么查出对应的多个字段信息

请参见下列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

in(v1,v2..vn) ，符合v1,v2,,,vn才能被查出

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

例如：

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

---