查询每行有两个互补值

Question

I have a subquery in a very large query that has to do the following

with an array of couples of clusters

array(array(c1, c2), array(c3, c4), array(c5, c6), array(c7, c8))

where for example c1 and c2 are complementary, c3 and c4 as well ..etc . and i have a table state :

id_state cluster   successfull   failed  success_ratio   
  1        c1           4            0       100%  
  2        c2           1            9       10%   
  3        c3           0            4        0%         
  4        c4           1            1        50% 

note that which cluster is coupled with the another is determined using the array above.

and the final output that i would like to have :

   cluster  successfull success_ratio                        
       c1         4        100%    (for the first pair)
       c4         1        50%      (for the second)

is there a way to do a query that gets the success_ratio of all the data by taking only from each couple the one with the success_ratio > 50% and only if both have success_ratio < 50% then just take the first one.

Is this even achievable using only a mysql query (i can't use query result since i want it as a subquery of another large query) ?

even if you can just suggest a starting point for a way to do it that would be appreciated.

Answer 1

It sounds like you just want the maximum success ratio for each pair.

select s.grp, max(success_ratio)
from state s join
     (select 'c1' as cluster, 1 as grp union all
      select 'c2', 1 union all
      select 'c3', 2 union all
      select 'c4', 2 union all
      select 'c5', 3 union all
      select 'c6', 3 union all
      select 'c7', 4 union all
      select 'c8', 4
     ) grps
     on s.cluster = grps.cluster
group by s.grp;

If you actually want the rows with the best success, then use a subquery:

select s.*
from (select s.grp,
      substring_index(group_concat(cluster order by success_ratio desc), ',', 1) as bestcluster
      from state s join
           (select 'c1' as cluster, 1 as grp union all
            select 'c2', 1 union all
            select 'c3', 2 union all
            select 'c4', 2 union all
            select 'c5', 3 union all
            select 'c6', 3 union all
            select 'c7', 4 union all
            select 'c8', 4
           ) grps
           on s.cluster = grps.cluster
      group by s.grp
     ) b join
     state s
     on s.cluster = b.cluster