union all与union关键字

最新推荐文章于 2024-02-27 11:20:24 发布

iteye_9244

最新推荐文章于 2024-02-27 11:20:24 发布

阅读量172

点赞数

分类专栏： ORALCE 文章标签：电子商务 oracle sql

本文链接：https://blog.youkuaiyun.com/iteye_9244/article/details/82582486

版权

ORALCE 专栏收录该内容

10 篇文章

订阅专栏

最近我在做电子商务项目里面遇到了一个需求，就是把秒杀与团购的商品一起查询出来。但是由于两个表里面的字段不一样所以秒杀商品与团购商品分来存储的，合并怎么合并了？？
最后我想到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" + 
"";

,但是我在执行之后老是报orc-000932异常，数据类型不一致。于是我就想是不是我要查询的字段是不是有类型问题，我一个个的对比发现没有问题。然后我在网上找了一下问题，原来是union关键字会会对结果集里面的商品数据进行帅选，把union换成union all就可以解决这个问题了.