幫助初學者的 SQL 查詢，最大列數

我有 2 張桌子，一張保存問題，另一張保存問題的答案，每個答案都有很多喜歡，我需要用最喜歡的答案列印每個問題。我添加了一張圖片以更好地說明問題。

到目前為止我寫的程式碼並不能很好地工作：

SELECT q.q_text,a.a_text

FROM answers as a
JOIN questions AS q
on a.q_id = q.id

WHERE (SELECT max(answers.likes) from answers)
GROUP BY q.id;

這種方法有一個很大的問題，如果你有兩個或多個相同數量的喜歡的答案，你會在結果集中看到兩個或更多。

如果每個問題只需要一個答案，則必須定義您喜歡的答案這將最好通過另一列實現，這將表明所有最大喜歡中的“最佳”。

CREATE TABLE questions (
  `Id` INTEGER,
  `question_text` VARCHAR(10)
);

INSERT INTO questions
  (`Id`, `question_text`)
VALUES
  ('1', 'question 1'),
  ('2', 'question 2');

CREATE TABLE answers (
  `Id` INTEGER,
  `question_id` INTEGER,
  `likes` INTEGER,
  `answer_text` VARCHAR(19)
);

INSERT INTO answers
  (`Id`, `question_id`, `likes`, `answer_text`)
VALUES
  ('11', '1', '3', 'question 1 answer 1'),
  ('12', '1', '20', 'question 1 answer 2'),
  ('13', '1', '5', 'question 1 answer 2'),
  ('21', '2', '500', 'question 2 answer 1'),
  ('22', '2', '10', 'question 2 answer 2'),
  ('23', '2', '80', 'question 2 answer 3');

SELECT 
    q.question_text, a.answer_text
FROM
    answers AS a
        JOIN
    questions AS q ON a.question_id = q.Id
WHERE
    a.id IN (SELECT 
            id
        FROM
            answers
        WHERE
            (`question_id` , likes) IN (SELECT 
                    `question_id`, MAX(likes)
                FROM
                    answers
                GROUP BY `question_id`));

問題文本 | answer_text 
:------------ | :------------------
問題1 | 問題 1 答案 2
問題2 | 問題 2 答案 1

db<>在這裡擺弄

SQL Server 上的簡短優化查詢：

Select
   A1.question_id,
   A1.answer_text
From answers A1
RIGHT JOIN
(
   Select
       question_id, Max(Likes) as likes
   From answers 
   Group by question_id
) A2
ON A1.question_id=A2.question_id and A1.likes=A2.likes
RIGHT JOIN questions Q
ON Q.Id = A1.question_Id;

結果：

引用自：https://dba.stackexchange.com/questions/276531