mysql - 按优先级选择

Question

表结构:

注册:

uuid | name | total

价格:

uuid | type | rate

注册率:

registration | rate

初始请求是:

select * from registration r
join registration_rate rr on rr.registration = r.uuid
join rate rt on rt.uuid = rr.rate
group by r.name,  rt.type

两个表(注册和费率)的 My SQL 结果是:

  uuid | name | rate | type
     1 | AAA  | 15   | U
     2 | BBB  | 20   | U
     3 | CCC  | 300  | F
     4 | AAA  | 250  | F

我想要这样的东西(如果汇率类型“F”存在，则显示)

  uuid | name | rate | type
     2 | BBB  | 20   | U
     3 | CCC  | 300  | F
     4 | AAA  | 250  | F

谢谢

已编辑:

我尝试了另一种有效的解决方案

select uuid, name, rate, (case rt.type when 2 then 2 else 1 end ) as type
from registration r
join registration_rate rr on rr.registration = r.uuid
join rate rt on rt.uuid = rr.rate
group by r.name,  rt.type

Answer 1

如果它是 F 行，则返回它。或者，使用 NOT EXISTS 验证其他同名行中没有 F。

select t1.*
from tablename t1
where type = 'F'
   or not exists (select * from tablename t2
                  where t2.name = t1.name
                    and t2.type = 'F')

替代解决方案:

select t1.*
from tablename t1
    join (select name, min(type) type
          from tablename
          group by name) t2
    ON t1.name = t2.name and t1.type = t2.type