为MySQL中的所有记录组选择一组记录中的特定记录

I have a table that contains many different records for products specifications, the products may appear more than once in the table as they have different colors. In order to display the products in a screen I need to select a list of them with the color YELLOW, but if YELLOW is not present I need color BLUE, or else I don't want this product.

Simplified Products example:

+----+--------+
| ID | NAME   |
+----+--------+
|  1 | Prod A |
|  2 | Prod B |
|  3 | Prod C |
|  4 | Prod D |
+----+--------+

Simplied Spec table:

+----+------------+--------+
| ID | ID_PRODUCT | COLOR  |
+----+------------+--------+
|  1 |          1 | BLUE   |
|  2 |          1 | YELLOW |
|  3 |          2 | RED    |
|  4 |          2 | PINK   |
|  5 |          3 | BLUE   |
|  6 |          3 | GRAY   |
|  7 |          4 | YELLOW |
+----+------------+--------+

Expected results:

+----+------------+--------+
| ID | ID_PRODUCT | COLOR  |
+----+------------+--------+
|  2 |          1 | YELLOW |
|  5 |          3 | BLUE   |
|  7 |          4 | YELLOW |
+----+------------+--------+

Raw SQL for this example:

CREATE TABLE `colors` (
  `ID` int(11) NOT NULL,
  `ID_PRODUCT` int(11) DEFAULT NULL,
  `COLOR` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `products` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `colors` VALUES (1,1,'BLUE'),(2,1,'YELLOW'),(3,2,'RED'),(4,2,'PINK'),(5,3,'BLUE'),(6,3,'GRAY'),(7,4,'YELLOW');

INSERT INTO `products` VALUES (1,'Prod A'),(2,'Prod B'),(3,'Prod C'),(4,'Prod D');

Here's one option using conditional aggregation:

select id_product, 
       max(case when color = 'YELLOW' then id
                when color = 'BLUE' then id
           end),
       max(case when color = 'YELLOW' then color
                when color = 'BLUE' then color
           end) 
from colors
where color in ('YELLOW','BLUE')
group by id_product

• SQL Fiddle Demo

Here is one method:

select c.*
from colors c
where c.color = 'YELLOW'
union all
select c.*
from colors c
where c.color = 'BLUE' and
      not exists (select 1
                  from colors c2
                  where c2.id_product = c.id_product and c2.color = 'YELLOW'
                 );

If it's always going to be blue and yellow comparison then I'd just use max() function, e.g.

select id, id_product, max(color) from colors
where color in ('BLUE','YELLOW')
group by id_product;