allproductsnofree.sql

来自「本程序是将ORACLE数据库中的表导出成为XML文件的程序。 1. 运行本程序」· SQL 代码 · 共 40 行

select f.name,
       f.field_rank,
       f.product_id,
       m.SERVICE_LEVEL,
       m.company_name_en,
       m.gmc_flag,
       k.supplier_id,
       k.IS_DIAMOND,
       k.create_date,
       k.product_name_en,
       k.catagory_id,
       k.s_pic,
       k.Approval_en,
       power(2,ascii(service_level)-64) power
  from 
  (   (select 6 field_rank, b.name, b.groupid, c.product_id
                  from keywords_en b, product_keywords1 c
                 where b.groupid = c.keyword_groupid)
        union (select 5 field_rank, b.name, b.groupid, c.product_id
                from keywords_en b, product_keywords2 c
               where b.groupid = c.keyword_groupid)
        union (select 4 field_rank, b.name, b.groupid, c.product_id
                from keywords_en b, product_keywords3 c
               where b.groupid = c.keyword_groupid)
        union (select 3 field_rank, b.name, b.groupid, c.product_id
                from keywords_en b, product_keywords4 c
               where b.groupid = c.keyword_groupid)
        union (select 20 field_rank, b.name, b.groupid, c.product_id
                from keywords_en b, product_keywords5 c
               where b.groupid = c.keyword_groupid)) f,
       product k,
       suppliers m
 where f.product_id = k.product_id and k.supplier_id = m.supplier_id and
       m.on_level = 'B' and k.Status = 'S'and m.SERVICE_LEVEL <> 'X'
 order by name asc,SERVICE_LEVEL asc,supplier_id asc,create_date desc