mysql - 选择 IF 查询

Question

我有一个简单的查询:

SELECT max(`id`) as `name_id` , LEFT(`name`,LENGTH(`name`)-2) AS `name`, `status` 
 FROM `users`
WHERE `site` = 1
  AND `category` = 'some'

我只需要选择那些，其中 supp == YES

SELECT `model`.`supp`
  FROM `model`, `users`
 WHERE `users`.`name_id` = `name_id_from_the_first_query`
   AND `model`.`model_id` = `users`.`model_id`;

因此，仅当第二个查询对当前 ID 返回 YES 时，我才需要第一个查询的返回值。

Answer 1

尝试，

SELECT  max(a.`id`) as `name_id` , 
        LEFT(`name`,LENGTH(a.`name`)-2) AS `name`, 
        a.`status`
FROM    `model` a 
        INNER JOIN `users` b
            ON a.`model_id` = b.`model_id`
WHERE    a.`site` = 1 AND
         a.`category` = 'some' AND
         b.`supp` = 'YES'
GROUP BY a.`name`, a.`status`