to welyngj(不做老实人) 去掉成本更大。我修改了一下:
SELECT shop_order.shop_order_no,
shop_order.shop_no,
shop_order.order_time,
shop_order.input_time,
shop_order.operator,
shop_order."REMARK",
shop_order.car_order,
shop_order.print_flag,
shop_order.send_time,
shop_order.last_modify_time,
shop_order.sign_flag
FROM shop_order, shop_order_goods, goods
WHERE shop_order_goods.goods_no = goods.goods_no
AND goods.goods_type = 'mooncake'
AND shop_order_goods."COUNT" <> 0
AND shop_order.shop_order_no = shop_order_goods.order_no 运行成本加大,但只查一列,成本可以接受
SELECT shop_order.shop_order_no,
FROM shop_order, shop_order_goods, goods
WHERE shop_order_goods.goods_no = goods.goods_no
AND goods.goods_type = 'mooncake'
AND shop_order_goods."COUNT" <> 0
AND shop_order.shop_order_no = shop_order_goods.order_no
SELECT shop_order.shop_order_no,
shop_order.shop_no,
shop_order.order_time,
shop_order.input_time,
shop_order.operator,
shop_order."REMARK",
shop_order.car_order,
shop_order.print_flag,
shop_order.send_time,
shop_order.last_modify_time,
shop_order.sign_flag
FROM shop_order, shop_order_goods, goods
WHERE shop_order_goods.goods_no = goods.goods_no
AND goods.goods_type = 'mooncake'
AND shop_order_goods."COUNT" <> 0
AND shop_order.shop_order_no = shop_order_goods.order_no 运行成本加大,但只查一列,成本可以接受
SELECT shop_order.shop_order_no,
FROM shop_order, shop_order_goods, goods
WHERE shop_order_goods.goods_no = goods.goods_no
AND goods.goods_type = 'mooncake'
AND shop_order_goods."COUNT" <> 0
AND shop_order.shop_order_no = shop_order_goods.order_no
用select * from ....试试看
goods: 944
shop_order: 25352
shop_order_goods: 5176012
shop_order.shop_no as shop_no,
shop_order.order_time as order_time,
shop_order.input_time as input_time,
shop_order.operator as operator,
shop_order.re as re,
shop_order.car_order as car_order,
shop_order.print_flag as print_flag,
shop_order.send_time as send_time,
shop_order.last_modify_time as last_modify_time,
shop_order.sign_flag as sign_flag
FROM shop_order,
shop_order_goods,
goods
WHERE shop_order_goods.goods_no = goods.goods_no
AND goods.goods_type = 'mooncake'
AND shop_order_goods."COUNT" <> 0
AND shop_order.shop_order_no = shop_order_goods.order_no
成本超大,改用查一列:
SELECT DISTINCT shop_order.shop_order_no as shop_order_no
FROM shop_order,
shop_order_goods,
goods
WHERE shop_order_goods.goods_no = goods.goods_no
AND goods.goods_type = 'mooncake'
AND shop_order_goods."COUNT" <> 0
AND shop_order.shop_order_no = shop_order_goods.order_no
成本为一万
goods
shop_order
shop_order_goodsgoods表中商品分两类:普通类,月饼类,列goods_type "mooncake"记录为月饼类
shop_order 为商店定单,定单中混合了所有商品,
shop_order_goods 表为每张定单的明细现select 结果为有月饼的定单
shop_order: 25352
shop_order_goods: 5176012 --是不是5百万条???记录超多数据库结构要修改至少一下字段要建索引,
shop_order_goods.goods_no
shop_order_goods.order_no
shop_order.shop_order_no 而且要针对你的数据分布情况写具体的SQL,比如
SELECT DISTINCT order_no
FROM shop_order_goods, goods
WHERE shop_order_goods.goods_no = goods.goods_no
AND goods.goods_type = 'mooncake'
AND shop_order_goods.COUNT <> 0) mooncakeShop_order
如果此子查询数据相对较少则你的第一种方法较好,反之第二种(连接)方法更好!!
可以建立视图把那个字查询放入试图,然后跟视图关联查询
SELECT DISTINCT order_no
FROM shop_order_goods, goods
WHERE shop_order_goods.goods_no = goods.goods_no
AND goods.goods_type = 'mooncake'
AND shop_order_goods.COUNT <> 0
將mooncakeShop_order做成一個臨時表(含主鍵)