allsuppliersofproduct.sql

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

select distinct f.name,
       f.field_rank,
       e.maxdate,
       s.supplier_id,
       s.COMPANY_NAME_EN,
       s.gmc_flag,
       s.service_level,
       sp.products_en
  from (select field_rank, name, groupid, product_id
          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 p,
       (select supplier_id, max(create_date) maxdate
          from product
         group by supplier_id) e,
       suppliers s,
       supplier_products sp
 where f.product_id = p.product_id and p.Status = 'S' and s.on_level = 'B' and
       p.supplier_id = s.supplier_id and p.supplier_id = e.supplier_id and
       p.supplier_id = sp.supplier_id
 order by name asc, service_level asc, supplier_id asc,  maxdate desc