Mysql通过行表中的行数据筛选主表数据

有订单头表和订单行表。筛选出行表中包含某些商品的头表

1. 示例表

test_order

test_order_line

2. 实现

EXISTS(推荐)

优点：
使用内联，主表字段很多的情况下，需要写越来越多的聚合函数，sql编写复杂

查询订单行中包含梨子的订单：

SELECT od.order_num,od.order_amt
FROM test_order od
WHERE EXISTS (
   SELECT * 
	 FROM test_order_line odl
	 WHERE od.order_num = odl.order_num
	 AND odl.line_product_id IN ('00001')
)

内联

先看下述sql：

SELECT od.order_num,od.order_amt,odl.line_product_name
FROM test_order od,test_order_line odl
WHERE
od.order_num = odl.order_num 
AND odl.line_product_id IN ('00001');

看似没有什么问题，但是如果不过滤product_id，或者过滤的多个商品单据中都存在就会出现重复行的情况

要加聚合函数，缺点就是随着查询字段的增多，要写很多聚合函数。

SELECT od.order_num,MAX(od.order_amt)
FROM test_order od,test_order_line odl
WHERE
od.order_num = odl.order_num 
AND odl.line_product_id IN ('00001','00002')
GROUP BY od.order_num;