CREATE DEFINER=`root`@`localhost` FUNCTION `compare_key_9`(`key1` CHAR(9), `key2` CHAR(9)) RETURNS tinyint(4)
BEGIN
DECLARE R INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
DECLARE a INT;
DECLARE b INT;


WHILE i<=LENGTH(key1) DO
    
	Set a = LOCATE(SUBSTRING(key1, i, 1),"0123456789abcdefghijklmnopqrstuvwxyz")-1;
	Set b = LOCATE(SUBSTRING(key2, i, 1),"0123456789abcdefghijklmnopqrstuvwxyz")-1;
    Set a = ABS(a-b)*ABS(a-b);
    
	IF a>100 THEN
	    Set R = R + 100;
   	ELSE
	    Set R = R + a;
   	END IF;    

	SET i=i+1;
END WHILE;

Set R = 100 - R / LENGTH(key1);

RETURN (R);
END

CREATE TABLE IF NOT EXISTS `Files_CRC` (
	`ID_Files` int(11) NOT NULL,
	`CRC` char(32) NOT NULL,
	`Key_Main` varchar(526) NOT NULL,
	`Key_red` char(9) NOT NULL,
	`Key_green` char(9) NOT NULL,
	`Key_blue` char(9) NOT NULL,
	`Key_grey` char(9) NOT NULL,
	`Key_color` char(9) NOT NULL
)

SELECT DISTINCT T10.*, T20.*, c.*
FROM Files T10
	INNER JOIN 
		(
            
            
            
SELECT T0.*, (T0.k_red+T0.k_green+T0.k_blue+T0.k_grey+T0.k_color) AS k_sum from (
    
    
						SELECT ID_Files
							 , compare_key_9(Key_red,   "1e43f4333") as k_red
							 , compare_key_9(Key_green, "5543f4335") as k_green
							 , compare_key_9(Key_blue,  "bbb3f4337") as k_blue
							 , compare_key_9(Key_grey,  "1777f4333") as k_grey
							 , compare_key_9(Key_color, "1e4399933") as k_color
						FROM Files_CRC 	
    
    
    
    ) T0 
					WHERE 
						(	    T0.k_red > 70
							and T0.k_green > 70
							and T0.k_blue > 70
							and T0.k_grey > 70
							and T0.k_color > 70
							and (  T0.k_red > 90
								or T0.k_green > 90						
								or T0.k_blue > 90						
								or T0.k_grey > 90						
								or T0.k_color > 90)						
						)
						or 
						(      T0.k_red > 97
							or T0.k_green > 97						
							or T0.k_blue > 97						
							or T0.k_grey > 97	
							or T0.k_color > 97)            
            
            
            ) T20 
	ON T10.ID = T20.ID_Files 
	ORDER BY T20.k_sum DESC
	LIMIT 0,10

SELECT 
  DISTINCT T10.*, 
  T20.*, 
  c.* 
FROM 
  Files T10 
  INNER JOIN (
    SELECT 
      T0.*, 
      (
        T0.k_red + T0.k_green + T0.k_blue + T0.k_grey + T0.k_color
      ) AS k_sum 
    from 
      (
        SELECT 
          ID_Files, 
          compare_key_9(Key_red, "1e43f4333") as k_red, 
          compare_key_9(Key_green, "5543f4335") as k_green, 
          compare_key_9(Key_blue, "bbb3f4337") as k_blue, 
          compare_key_9(Key_grey, "1777f4333") as k_grey, 
          compare_key_9(Key_color, "1e4399933") as k_color 
        FROM 
          Files_CRC
      ) T0 
    WHERE 
      (
        T0.k_red > 70 
        and T0.k_green > 70 
        and T0.k_blue > 70 
        and T0.k_grey > 70 
        and T0.k_color > 70 
        and (
          T0.k_red > 90 
          or T0.k_green > 90 
          or T0.k_blue > 90 
          or T0.k_grey > 90 
          or T0.k_color > 90
        )
      ) 
      or (
        T0.k_red > 97 
        or T0.k_green > 97 
        or T0.k_blue > 97 
        or T0.k_grey > 97 
        or T0.k_color > 97
      )
  ) T20 ON T10.ID = T20.ID_Files 
ORDER BY 
  T20.k_sum DESC 
LIMIT 
  0, 10

SELECT 
  DISTINCT T10.*, 
  T20.* 
FROM 
  Files T10 
  INNER JOIN (
    SELECT 
      T0.*, 
      (
        T0.k_red + T0.k_green + T0.k_blue + T0.k_grey + T0.k_color
      ) AS k_sum 
    from (
        SELECT 
          ID_Files, 
          sum(k_red) as k_red, 
          sum(k_green) as k_green, 
          sum(k_blue) as k_blue, 
          sum(k_grey) as k_grey, 
          sum(k_color) as k_color
        FROM 
          (
            SELECT 
              ID_Files, 
              compare_key_9(Key_red, "1e43f4333") as k_red, 
              0 as k_green, 
              0 as k_blue, 
              0 as k_grey, 
              0 as k_color 
            FROM 
              Files_CRC 
            UNION ALL 
            SELECT 
              ID_Files, 
              0 as k_red, 
              compare_key_9(Key_green, "5543f4335") as k_green, 
              0 as k_blue, 
              0 as k_grey, 
              0 as k_color
            FROM 
              Files_CRC 
            UNION ALL 
            SELECT 
              ID_Files, 
              0 as k_red, 
              0 as k_green, 
              compare_key_9(Key_blue, "bbb3f4337") as k_blue, 
              0 as k_grey, 
              0 as k_color
            FROM 
              Files_CRC 
            UNION ALL 
            SELECT 
              ID_Files, 
              0 as k_red, 
              0 as k_green, 
              0 as k_blue, 
              compare_key_9(Key_grey, "1777f4333") as k_grey, 
              0 as k_color
            FROM 
              Files_CRC 
            UNION ALL 
            SELECT 
              ID_Files, 
              0 as k_red, 
              0 as k_green, 
              0 as k_blue, 
              0 as k_grey, 
              compare_key_9(Key_color, "1e4399933") as k_color 
             FROM 
               Files_CRC
           ) AS T
               
        GROUP BY ID_Files ) T0 
        WHERE 
          (
            LEAST(
              T0.k_red, T0.k_green, T0.k_blue, T0.k_grey, 
              T0.k_color
            ) > 70 
            AND GREATEST(
              T0.k_red, T0.k_green, T0.k_blue, T0.k_grey, 
              T0.k_color
            ) > 90
          ) 
          OR GREATEST(
            T0.k_red, T0.k_green, T0.k_blue, T0.k_grey, 
            T0.k_color
          ) > 97
      ) T20 ON T10.ID = T20.ID_Files 
    ORDER BY 
      T20.k_sum DESC 
    LIMIT 
      0, 10
     

            SELECT 
              ID_Files, 
              compare_key_9(Key_red, "1e43f4333") as k_red, 
              0 as k_green, 
              0 as k_blue, 
              0 as k_grey, 
              0 as k_color 
            FROM 
              Files_CRC 
 

BEGIN
DECLARE R INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
DECLARE a INT;
DECLARE L INT;
SET L = LENGTH(key1);
WHILE i<=L DO
	Set a = ABS(ORD(SUBSTRING(key1, i, 1))-ORD(SUBSTRING(key2, i, 1)));
	IF a>10 THEN Set R = R + 100; ELSE Set R = R + a * a; END IF;    
	SET i=i+1;
END WHILE;
Set R = 100 - R / L;
RETURN (R);
END