sql - 你如何进行 Oracle SQL 查询

Question

这张表与普通模式相当落后，我不确定如何从中获取我需要的数据。

这里是一些示例数据，

Value (column)          Info (column)
---------------------------------------------
Supplier_1              'Some supplier'
Supplier_1_email        'foo@gmail.com'
Supplier_1_rating       '5'
Supplier_1_status       'Active'
Supplier_2              'Some other supplier'
Supplier_2_email        'bar@gmail.com'
Supplier_2_rating       '4'
Supplier_2_status       'Active'
Supplier_3              'Yet another supplier'

...

我需要查询以找到评级最高且当前状态为“有效”的供应商的电子邮件。

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;