最近我在做电子商务项目里面遇到了一个需求，就是把秒杀与团购的商品一起查询出来。但是由于两个表里面的字段不一样所以秒杀商品与团购商品分来存储的，合并怎么合并了？？
最后我想到oracle里面的关键字union，这个关键字是用来查询并集的，所以我就用了union来把两个表里面数据查询出来，sql如下：
最后我想到oracle里面的关键字union，这个关键字是用来查询并集的，所以我就用了union来把两个表里面数据查询出来，sql如下：
"select A.*\n" +
" from (\n" +
" select A.SECKILL_ID ID,\n" +
" A.COMMODITY_ID,\n" +
" A.COMMODITY_NAME,\n" +
" A.SELL_PRICE,\n" +
" A.BUY_USER_CNT,\n" +
" A.PRICE,\n" +
" A.isTimeEnd,\n" +
" A.COUNT,\n" +
" A.BRAND_ID,\n" +
" 1 TYPE,\n" +
" discount,\n" +
" A.BEGIN_TIME,\n" +
" A.CONTENT CONTENT,\n" +
" TO_CHAR(discount * 10, '0.99') show_discount\n" +
" from (select s.*,\n" +
" sc.BRAND_ID,\n" +
" sc.COMMODITY_NAME,\n" +
" case\n" +
" when begin_time < sysdate and end_time > sysdate then\n" +
" 0\n" +
" else\n" +
" 1\n" +
" end isTimeEnd,\n" +
" sc.sell_price,\n" +
" nvl(cnt, 0) + nvl(back_count, 0) BUY_USER_CNT,\n" +
" s.price / sc.sell_price discount\n" +
" from seckill s\n" +
" join (select CSC.SORT_ID,\n" +
" C.COMMODITY_ID,\n" +
" C.COMMODITY_NAME,\n" +
" SC.SELL_PRICE,\n" +
" SC.SHOP_COMMODITY_ID,\n" +
" C.BRAND_ID\n" +
" from COMMODITY C\n" +
" join SHOP_COMMODITY SC on C.COMMODITY_ID =\n" +
" SC.COMMODITY_ID\n" +
" join COMMODITY_SORT_CONTACT CSC on CSC.COMMODITY_ID =\n" +
" C.COMMODITY_ID\n" +
" join (SELECT *\n" +
" FROM sort\n" +
" START WITH SORT_ID = 1\n" +
" connect by P_ID = prior SORT_ID\n" +
" and STATE = 1) s on S.SORT_ID =\n" +
" CSC.SORT_ID\n" +
" join COMMODITY_BRAND CB on C.BRAND_ID = CB.BRAND_ID\n" +
" where S.STATE = 1\n" +
" and SC.STATE = 1\n" +
" and sc.SELL_PRICE > 0\n" +
" and C.STATE = 1\n" +
" and C.IS_SHOW = 1\n" +
" and CB.STATE = 1\n" +
" and SC.SALE_ENABLE_COUNT > 0\n" +
" and SC.SELL_PRICE > 0\n" +
" and (c.IS_SCORE is null or c.IS_SCORE != 1)) sc on sc.commodity_id =\n" +
" s.commodity_id\n" +
" left join (select count(seckill_id) cnt, seckill_id\n" +
" from seckill_order\n" +
" group by seckill_id) o on o.seckill_id =\n" +
" s.seckill_id\n" +
" where s.state = 1) A\n" +
" union \n" +
" select A.GROUP_BUYING_ID ID,\n" +
" A.COMMODITY_ID,\n" +
" A.COMMODITY_NAME,\n" +
" A.SELL_PRICE,\n" +
" A.BUY_USER_CNT,\n" +
" A.PRICE,\n" +
" A.isTimeEnd,\n" +
" A.BRAND_ID,\n" +
" A.COUNT,\n" +
" 2 TYPE,\n" +
" discount,\n" +
" A.BEGIN_TIME,\n" +
" A.GROUP_BUYING_TEXT CONTENT,\n" +
" TO_CHAR(discount * 10, '0.99') show_discount\n" +
" from (select group_buying_text CONTENT,\n" +
" group_buying_price price,\n" +
" s.*,\n" +
" sc.BRAND_ID,\n" +
" sc.COMMODITY_NAME,\n" +
" case\n" +
" when begin_time < sysdate and end_time > sysdate then\n" +
" 0\n" +
" else\n" +
" 1\n" +
" end isTimeEnd,\n" +
" sc.sell_price,\n" +
" nvl(cnt, 0) + nvl(back_count, 0) BUY_USER_CNT,\n" +
" s.group_buying_price / sc.sell_price discount\n" +
" from group_buying s\n" +
" join (select CSC.SORT_ID,\n" +
" C.COMMODITY_ID,\n" +
" C.COMMODITY_NAME,\n" +
" SC.SELL_PRICE,\n" +
" SC.SHOP_COMMODITY_ID,\n" +
" C.BRAND_ID\n" +
" from COMMODITY C\n" +
" join SHOP_COMMODITY SC on C.COMMODITY_ID =\n" +
" SC.COMMODITY_ID\n" +
" join COMMODITY_SORT_CONTACT CSC on CSC.COMMODITY_ID =\n" +
" C.COMMODITY_ID\n" +
" join (SELECT *\n" +
" FROM sort\n" +
" START WITH SORT_ID = 1\n" +
" connect by P_ID = prior SORT_ID\n" +
" and STATE = 1) s on S.SORT_ID =\n" +
" CSC.SORT_ID\n" +
" join COMMODITY_BRAND CB on C.BRAND_ID = CB.BRAND_ID\n" +
" where S.STATE = 1\n" +
" and SC.STATE = 1\n" +
" and sc.SELL_PRICE > 0\n" +
" and C.STATE = 1\n" +
" and C.IS_SHOW = 1\n" +
" and CB.STATE = 1\n" +
" and SC.SALE_ENABLE_COUNT > 0\n" +
" and SC.SELL_PRICE > 0\n" +
" and (c.IS_SCORE is null or c.IS_SCORE != 1)) sc on sc.commodity_id =\n" +
" s.commodity_id\n" +
" left join (select count(group_buying_id) cnt,\n" +
" group_buying_id\n" +
" from (select MEMBER_ID, group_buying_id\n" +
" from GROUP_BUYING_MEMBER\n" +
" where state != 2\n" +
" group by group_buying_id, member_id)\n" +
" group by group_buying_id) o on o.group_buying_id =\n" +
" s.group_buying_id\n" +
" where s.state = 1) A) A\n" +
" order by BEGIN_TIME desc, type asc, ID DESC\n" +
"\n" +
"";
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
