SQL语句总结

ps：关于sql语句的基础知识请百度，关于sql语句join相关的内容推荐http://blog.youkuaiyun.com/mangmang2012/article/details/8017034

1.多表联合的效率

四个表联合查询，起初我用的是

insert overwrite table auto_test.final_customerwishtable
select a.product_id,a.customer_id, a.entered_price,c.sale_price,a.entered_stock_status,b.stock_status
from gwjdb.customerwishtable a join gwjdb.product_stock b join gwjdb.product_price c join gwjdb.products_core_search d 
on a.product_id=b.product_id and a.product_id=c.product_id and a.product_id=d.product_id
where b.stock_status=1;

后来用的是

insert overwrite table auto_test.final_customerwishtable
select a.product_id,a.customer_id, a.entered_price,c.sale_price,a.entered_stock_status,b.stock_status
from gwjdb.customerwishtable a 
join (select product_id,stock_status from gwjdb.product_stock where stock_status=1) b on a.product_id=b.product_id 
join gwjdb.product_price c on a.product_id=c.product_id 
join gwjdb.products_core_search d on a.product_id=d.product_id；

900多万的数据量，前者执行2个多小时无果，后者用了十五分钟左右。说明join后面紧接着on效率会高些。