Untitled

mail@pastecode.io avatar
unknown
plain_text
2 years ago
1.1 kB
2
Indexable
Never
SELECT t7.pid1,
       t7.pid2,
       Sum(min_of)/Sum(max_of)
FROM
  (SELECT t4.pid1,
          t4.pid2,
          CASE
              WHEN IFNULL(t5.frequ, 0) > IFNULL(t6.frequ, 0) THEN IFNULL(t6.frequ, 0)
              ELSE IFNULL(t5.frequ, 0)
          END AS min_of,
          CASE
              WHEN IFNULL(t5.frequ, 0) > IFNULL(t6.frequ, 0) THEN IFNULL(t5.frequ, 0)
              ELSE IFNULL(t6.frequ, 0)
          END AS max_of
   FROM
     (SELECT t1.proff_id pid1,
             t2.proff_id pid2,
             t3.skill_id
      FROM
        (SELECT DISTINCT proff_id
         FROM proff_stat) AS t1
      LEFT JOIN
        (SELECT DISTINCT proff_id
         FROM proff_stat) AS t2 ON t1.proff_id < t2.proff_id
         LEFT JOIN
        (SELECT DISTINCT skill_id
         FROM proff_stat) AS t3
         ON 1 = 1
      WHERE t2.proff_id IS NOT NULL) AS t4 
LEFT JOIN proff_stat AS t5 ON t4.proff_id = t5.proff_id
AND t4.skill_id = t5.skill_id
LEFT JOIN proff_stat AS t6 ON t4.proff_id = t6.proff_id
AND t4.skill_id = t6.skill_id) AS t7
GROUP BY t7.pid1,
         t7.pid2