比较同一列中的两个值

I need to compare the value in a single column in a single table. Here is a sample table:

我需要比较单个表中单个列中的值。这是一个示例表:

ID  Cat     Color
======================
1   red     maroon
2   red     orange
3   red     pink
4   blue    violet
5   blue    purple
6   blue    indigo
7   green   puke green
8   green   hunter green

I am given 2 colors from the Color column. I need to know if they belong to the same Cat column. For example, I will be given maroon and orange. I need the value red returned. Violet and purple should return blue. Puke green and violet should return null.

我从色列中得到两种颜色。我想知道他们是否属于同一个Cat专栏。例如，我将被给予栗色和橙色。我需要返回值red。紫色和紫色应该返回蓝色。Puke green和violet应该返回null。

So far I have the following SQL but it's not exactly what I am looking for, especially with the Limit 1. I am looking for a single query to return Cat field without using Limit 1.

到目前为止，我有以下SQL，但这不是我想要的，特别是在限制1的情况下。我正在寻找一个查询来返回Cat字段，而不使用Limit 1。

SELECT Cat
From foo
WHERE 
Color = 'maroon'
and
Color = 'orange'
LIMIT 1

5 个解决方案

#1

In addition to Beginner's answer, it's possible to solve this problem without a GROUP_CONCAT:

除了初学者的回答之外，不用GROUP_CONCAT就可以解决这个问题:

SELECT cat
FROM foo
WHERE color IN ('maroon', 'orange')
GROUP BY cat
HAVING COUNT(*) = 2
;

This works by selecting all cats with the specified colors. When we group them, the cats that appear multiple times (the HAVING clause) are the records you want to keep.

这可以通过选择所有带有指定颜色的猫来实现。当我们对它们进行分组时，多次出现的猫(have子句)是您希望保存的记录。

Note: the number using the HAVING clause should match the number of colors you're searching for.

注意:使用have子句的数字应该与您正在搜索的颜色数目相匹配。

#2

You can try this:

你可以试试这个:

SELECT x.cat 
FROM (
  SELECT cat, GROUP_CONCAT(color) AS colors
  FROM tablename
  GROUP BY cat) AS x
WHERE FIND_IN_SET('maroon', x.colors) > 0
  AND FIND_IN_SET('orange', x.colors) > 0

Edit 1: Another Alternative

编辑1:另一种选择

SELECT IF(
          FIND_IN_SET('maroon', GROUP_CONCAT(color)) > 0 
          AND FIND_IN_SET('orange', GROUP_CONCAT(color)) > 0 , cat, NULL
       ) AS cat
FROM tablename
GROUP BY cat

#3

You need to JOIN the table with itself, on the primary keys of the table (also called Self-Join).

您需要在表的主键(也称为自连接)上将表与自己连接。

Try this:

试试这个:

SELECT A.Cat
From foo A, foo B
WHERE 
A.ID = B.ID 
and A.Cat = B.Cat and
A.Color = 'maroon'
and
B.Color = 'orange'

#4

Try like this using GROUP BY clause

尝试使用GROUP BY子句

select case when count(*) > 0 then cat else NULL end as MyCat_Column
from (
select Cat
from table1
where color in ('maroon','orange')
group by cat
having count(distinct color) >= 2
) tab;

#5

DECLARE @Cat varchar(50) = null

select @Cat = Cat
from foo f1
JOIN foo f2 on f1.Cat = f2.Cat
where f1.Color = 'maroon'
and f2.Color = 'orange'

select @Cat

Ideally you should have an associated table of colors so that you can do a JOIN and a more specific where.

理想情况下，您应该有一个相关联的颜色表，以便您可以执行连接和更具体的where。

#1