mysql - 如何用更少的代码编写 SQL 请求？

Question

我有这样的代码:

SELECT 
  `cabinet`.`name_test`, 
  `answers`.`answer_id`,
  (
    SELECT `answer_id` 
    FROM `answers` 
    WHERE `user_id` = '353281' AND `answers`.`test_id` = `cabinet`.`test_id`
  ) as answer_2
FROM `cabinet` 
INNER JOIN `answers`
WHERE
  `user_id` = '184918649' 
  AND `friend_id` = '353281' 
  AND `answers`.`test_id` = `cabinet`.`test_id`

如何不写两次:answers.test_id = cabinet.test_id?

Answer 1

这并没有减少代码，但您也可以通过两次加入 answers 表来做到这一点:

SELECT 
  c.`name_test`, 
  a1.`answer_id` answer_1,
  a2.`answer_id` answer_2
FROM `cabinet` c
INNER JOIN `answers` a1
  ON c.`test_id` = a1.`test_id`
  AND a1.`user_id` = '184918649' 
  AND  `friend_id` = '353281'
INNER JOIN `answers` a2
  ON c.`test_id` = a2.`test_id`
  AND a2.`user_id` = '353281'

或者你可以使用子查询:

SELECT 
  c.`name_test`, 
  a1.`answer_id` answer_1,
  a2.`answer_id` answer_2
FROM `cabinet` c
INNER JOIN
(
  select `answer_id`
  from `answers`
  where `user_id` = '184918649' 
    AND  `friend_id` = '353281'
) a1
  ON c.`test_id` = a1.`test_id`
INNER JOIN
(
  select `answer_id`
  from `answers`
  where `user_id` = '353281'
) a2
  ON c.`test_id` = a2.`test_id`;