根据列值优先级选择唯一记录 [英] Select unique record based on column value priority

问题描述

这是我以前的问题的延续，此处.

This is a continuation of my previous question here.

在以下示例中:

id       PRODUCT ID    COLOUR
1        1001          GREEN
2        1002          GREEN
3        1002          RED
4        1003          RED

给出一个产品ID，我只想检索一个记录-如果存在，则记录为绿色，否则为红色.听起来我需要以某种方式使用DISTINCT，但是我不知道如何提供优先级规则.

Given a product ID, I want to retrieve only one record - that with GREEN colour, if one exists, or the RED one otherwise. It sounds like I need to employ DISTINCT somehow, but I don't know how to supply the priority rule.

我敢肯定，这是非常基础的，但是我的SQL技能不仅仅生锈.

Pretty basic I'm sure, but my SQL skills are more than rusty..

编辑:谢谢大家.请再提出一个问题:如何使它与多个记录一起使用，即如果WHERE子句返回的不止一个记录? LIMIT 1将限制整个集合，而我想要的是限制每个产品.

Thank you everybody. One more question please: how can this be made to work with multiple records, ie. if the WHERE clause returns more than just one record? The LIMIT 1 would limit across the entire set, while what I'd want would be to limit just within each product.

例如，如果我有类似SELECT * FROM table的WHERE productID，例如"1％" ...我如何检索每个唯一的产品，但仍要遵守颜色优先级(GREEN> RED)?

For example, if I had something like SELECT * FROM table WHERE productID LIKE "1%" ... how can I retrieve each unique product, but still respecting the colour priority (GREEN>RED)?

推荐答案

SELECT * 
FROM yourtable
WHERE ProductID = (your id)
ORDER BY colour 
LIMIT 1

(您会看到，绿色将在红色之前出现.LIMIT子句仅返回一条记录)

(Green will come before Red, you see. The LIMIT clause returns only one record)

对于以后的编辑，您可以执行此操作

For your subsequent edit, you can do this

select yourtable.*
from
    yourtable
    inner join 
    (select productid, min(colour) mincolour 
    from yourtable
    where productid like '10%'
    group by productid) v
    on yourtable.productid=v.productid
    and yourtable.colour=v.mincolour    

这篇关于根据列值优先级选择唯一记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！