确定MySQL中单行的最大公共值

Question

I have a table like this:

-----+--------+--------+-----+-----+-----+-----+-----+-----+-----+-----+------------
r_id |  r_user| module | q_1 | q_2 | q_3 | q_4 | q_5 | q_6 | q_7 | q_8 |  q_9
-----+--------+--------+-----+-----+-----+-----+-----+-----+-----+-----+------------
 1   |  test  | 1      | g   | r   | r   | y   | g   | g   | y   | r   |  g
-----+--------+--------+-----+-----+-----+-----+-----+-----+-----+-----+------------
 2   |  test-2| 1      | r   | r   | g   | r   | r   | y   | y   | r   |  g

Is there any way to determine the maximum common value for each row? For example, the first row should result "g" and the second row should result "r".

I can't figure it out how to do that in MYSQL. Any help on that?

Answer 1

It appears you are working with fixed values or "r", "g" and "y" values. If so, this should work for you. The inner "PQ" (pre-Query) is getting each row other data elements and then summing each column with 1 IF it's value is the "r", "g" or "y" respectively into 3 count columns.

From that, I'm then using the "Greatest" function which returns the highest value from a given list provided. If you have all 3 with 3 of same color, they will all be flagged as highest... or even if a 4/4/1 split, two will show as high count.

I didn't know your table name so I just called it "freq1"

select
      PQ.r_id,
      PQ.r_user,
      PQ.module,
      PQ.gCnt,
      PQ.rCnt,
      PQ.yCnt,
      if( PQ.gCnt = greatest( PQ.gCnt, PQ.rCnt, PQ.yCnt ), 'yes', '   ' ) as HighG,
      if( PQ.rCnt = greatest( PQ.gCnt, PQ.rCnt, PQ.yCnt ), 'yes', '   ' ) as HighR,
      if( PQ.yCnt = greatest( PQ.gCnt, PQ.rCnt, PQ.yCnt ), 'yes', '   ' ) as HighY,
      greatest( PQ.gCnt, PQ.rCnt, PQ.yCnt ) as HighCnt
   from
( select 
      r_id, r_user, module,
        if( q_1 = 'g', 1, 0 )
      + if( q_2 = 'g', 1, 0 )
      + if( q_3 = 'g', 1, 0 )
      + if( q_4 = 'g', 1, 0 )
      + if( q_5 = 'g', 1, 0 )
      + if( q_6 = 'g', 1, 0 )
      + if( q_7 = 'g', 1, 0 )
      + if( q_8 = 'g', 1, 0 )
      + if( q_9 = 'g', 1, 0 ) as gCnt,
        if( q_1 = 'r', 1, 0 )
      + if( q_2 = 'r', 1, 0 )
      + if( q_3 = 'r', 1, 0 )
      + if( q_4 = 'r', 1, 0 )
      + if( q_5 = 'r', 1, 0 )
      + if( q_6 = 'r', 1, 0 )
      + if( q_7 = 'r', 1, 0 )
      + if( q_8 = 'r', 1, 0 )
      + if( q_9 = 'r', 1, 0 ) as rCnt,
        if( q_1 = 'y', 1, 0 )
      + if( q_2 = 'y', 1, 0 )
      + if( q_3 = 'y', 1, 0 )
      + if( q_4 = 'y', 1, 0 )
      + if( q_5 = 'y', 1, 0 )
      + if( q_6 = 'y', 1, 0 )
      + if( q_7 = 'y', 1, 0 )
      + if( q_8 = 'y', 1, 0 )
      + if( q_9 = 'y', 1, 0 ) as yCnt
   from freq1 ) PQ

results created

r_id   r_user  module  gCnt   rCnt   yCnt   HighG   HighR   HighY   HighCnt
1      test    1       4      3      2      yes                     4
2      test-2  1       2      5      2              yes             5