查询如何制作Oracle SQL查询

Question

这个表格与正常的模式相比有些倒置，我不确定如何从中获取所需数据。

以下是一些示例数据，

Value (column)          Info (column)
---------------------------------------------
Supplier_1              'Some supplier'
Supplier_1_email        '[e-mail]'
Supplier_1_rating       '5'
Supplier_1_status       'Active'
Supplier_2              'Some other supplier'
Supplier_2_email        '[e-mail]'
Supplier_2_rating       '4'
Supplier_2_status       'Active'
Supplier_3              'Yet another supplier'

我需要一个查询来查找评分最高且当前状态为“活跃”的供应商的电子邮件。

- PherricOxide

---

你可能想要创建一个中间的“供应商”视图，以帮助自己和其他人在未来查询这个表。 - Mike Atlas

亲爱的上帝，有人拿EAV并将其变得更糟了。 - user359040

Answer 1

select 
    m.sup_email, r.sup_rating 
from 
    (select substr(value, 1, length(value) - length('_email') as sup_name, info as sup_email from table where value like '%_email') as m 
left join 
    (select substr(value, 1, length(value) - length('_rating') as sup_name), info as sub_rating from table where value like '%_rating') as r on m.sup_name = r.sup_name 
order by 
   sup_rating desc 
limit 
    1;

- mitch

---

哎呀，这正是我试图弄清楚的问题... 但你漏掉了“Active”条件... - Dan J

哇，当你发布这个问题时，我正在打一个丑陋的答案，但是你的问题改变了我的想法。你是想用供应商名称替换“value”，还是Oracle足够聪明，在计算连接时内部填充此变量作为缓冲变量？ - Steve Perkins

@OMG Ponies: 与你先计算供应商数量，然后为每个供应商在查询中剪切粘贴新的 UNION 块的做法相比，这种方法要更有效率些。哈哈……至少这个查询相对于手动浏览数据库来说更有效率了，而不是更低效！ :) - Steve Perkins

Answer 2

如果需要一次性解决方案，请尝试：

select "email" from 
(select 
    substr("value", 1, 8 + instr(substr("value", 10, length("value")-9),'_')) "supplier", 
    max(case when "value" like '%_status' then "info" end) as "status",
    max(case when "value" like '%_rating' then cast("info" as integer) end) as "rating",
    max(case when "value" like '%_email' then "info" end) as "email"
from "table" t
where "value" like '%_rating' or "value" like '%_email' or "value" like '%_status'
group by substr("value", 1, 8 + instr(substr("value", 10, length("value")-9),'_'))
having max(case when "value" like '%_status' then "info" end) = 'Active'
order by 3 desc
) where rownum = 1

（所有列名都用双引号括起来，因为有些是保留字。）

- user359040

Answer 3

在Mike的优秀建议基础上进一步扩展：

CREATE VIEW supplier_names AS
  SELECT SUBSTR(Value,INSTR(Value,'_')+1) AS supplier_id
        ,Info AS supplier_name
  FROM   the_table
  WHERE  INSTR(Value,'_',1,2) = 0;

CREATE VIEW supplier_emails AS
  SELECT SUBSTR(Value,INSTR(Value,'_')+1,INSTR(Value,'_',1,2)-INSTR(Value,'_')-1)
         AS supplier_id
        ,Info AS supplier_email
  FROM   the_table
  WHERE  Value LIKE '%email';

CREATE VIEW supplier_ratings AS
  SELECT SUBSTR(Value,INSTR(Value,'_')+1,INSTR(Value,'_',1,2)-INSTR(Value,'_')-1)
         AS supplier_id
        ,Info AS supplier_rating
  FROM   the_table
  WHERE  Value LIKE '%rating';

CREATE VIEW supplier_statuses AS
  SELECT SUBSTR(Value,INSTR(Value,'_')+1,INSTR(Value,'_',1,2)-INSTR(Value,'_')-1)
         AS supplier_id
        ,Info AS supplier_rating
  FROM   the_table
  WHERE  Value LIKE '%status';

这些查询的性能很差，建议您考虑创建一些虚拟列或至少是基于函数的索引来优化这些查询。

- Jeffrey Kemp