-- Temprary Table の作成 --
WITH trump AS (SELECT * FROM ( VALUES
('♦',1),('♦',2),('♦',3),('♦',4),('♦',5),('♦',6),('♦',7),('♦',8),('♦',9),('♦',10),('♦',11),('♦',12),('♦',13),
('♤',8),('♤',9),('♤',10),('♤',11),('♤',12),('♤',13),
('♣',1),('♣',3),('♣',5),('♣',7),('♣',9),('♣',11),('♣',13),
('♡',2),('♡',4),('♡',6),('♡',8),('♡',10),('♡',12),
('joker',0)
) AS t(symbol,number))
-- 以下，temporary table: trump を用いてのクエリ --
SELECT number1, number2, COUNT(1) AS cnt
FROM
(
  SELECT n1.symbol,n2.symbol, n1.number AS number1, n2.number AS number2
  FROM
  (
    SELECT number, symbol
    FROM trump
    WHERE symbol!='joker'
  ) n1
  JOIN
  (
    SELECT number, symbol
    FROM trump
    WHERE symbol!='joker'
  ) n2
  ON n1.symbol = n2.symbol
  HAVING n1.number < n2.number
) tmp
GROUP BY number1, number2
ORDER BY cnt DESC, number1, number2