根据列优先级选择记录 [英] Select records based on column priority

问题描述

首先，这个问题的标题太可怕了，但是我没有找到更好的方式来描述我的问题.

First of all, the title of this question is horrible, but I didn't find a better way to describe my issue.

可能有一个非常简单的方法来执行此操作，但我无法弄清楚.这与此问题非常相似，但我正在继续sqlite3(iOS)，所以我怀疑我的选择受到更多限制.

There's probably a very easy way to do this, but I couldn't figure it out. This is very similar to this question, but I'm running on sqlite3 (iOS) so I suspect my options are much more limited.

我有一张带有产品记录的表格.所有记录都有一个ID(请注意:我不是在说行ID，而是每个产品唯一的标识号).某些产品在表中可能有两个条目(两者都具有相同的ID).唯一的区别是在特殊列中(假设COLOR列可以是RED或GREEN).

I have a table with product records. All records have an ID (note: I'm not talking about the row ID, but rather an identification number unique to each product). Some products may have two entries in the table (both with the same ID). The only difference would be in a special column (let's say column COLOUR can be either RED or GREEN).

我想做的是基于COLOUR的值创建一个唯一产品列表，如果同一产品同时存在GREEN和RED记录，则优先使用GREEN.

What I want to do is create a list of unique products based on the value of COLOUR, with priority to GREEN if both GREEN and RED records exist for the same product.

简而言之，如果我有以下情况:

In short, if I have the following case:

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

我希望我的SELECT返回第1、2和4行.如何实现呢?

I would like my SELECT to return the rows 1, 2 and 4. How can I achieve this?

我当前的方法是拥有单独的表，并手动进行联接，但这显然是一个非常糟糕的主意.

My current approach is to have separate tables, and do the join manually, but obviously this is a very bad idea..

注意:我尝试从此处使用相同的方法:

Note: I've tried to use the same approach from here:

SELECT * 
  FROM xx
 WHERE f_COLOUR = "GREEN"
UNION ALL
SELECT * 
  FROM xx 
 WHERE id not in (SELECT distinct id 
                    FROM xx 
                   WHERE f_COLOUR = "GREEN");

但是我得到的结果是行1,2,3,4，而不是1,2,4.我想念什么?

But the result I'm getting is rows 1,2,3,4 instead of just 1,2,4. What am I missing?

编辑:请再问一个问题:如何处理记录的子集，例如是否要代替整个表格来过滤一些记录?

One more question please: how can this be made to work with a subset of records, ie. if instead of the entire table I wanted to filter some records?

例如，如果我有类似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)?

推荐答案

您的查询几乎是正确的.只需使用PRODUCTID而不是ID.

Your query is nearly correct. Just use PRODUCTID and not ID.

SELECT * 
FROM xx
WHERE f_COLOUR = "GREEN"
UNION
SELECT * 
FROM xx 
WHERE PRODUCTID not in 
                (SELECT PRODUCTID
                 FROM xx 
                 WHERE f_COLOUR = "GREEN");

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