为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');

我有一个表格，其中包含许多不同的产品规格记录，产品可能会在表格中出现多次他们有不同的颜色。为了在屏幕上显示产品，我需要选择颜色为黄色的列表，但如果黄色不存在，我需要颜色为蓝色，否则我不想要这个产品。 p> \ n

Simplified Products示例： p>

 
 + ---- + -------- + 
 |  ID |  NAME | 
 + ---- + -------- + 
 |  1 | 产品A | 
 |  2 | 产品B | 
 |  3 |  Prod C | 
 |  4 |  Prod D | 
 + ---- + -------- + 
  pre> 
 
  Simplied Spec table： p> 
 
 
 
 +  -  --- + ------------ + -------- + \ N |  ID |  ID_PRODUCT | 颜色| 
 + ---- + ------------ + -------- + 
 |  1 |  1 | 蓝色| 
 |  2 |  1 | 黄色| 
 |  3 |  2 |  RED | 
 |  4 |  2 | 粉红色| 
 |  5 |  3 | 蓝色| 
 |  6 |  3 | 灰色| 
 |  7 |  4 | 黄色| 
 + ---- + ------------ + -------- + 
  pre> 
 
 预期结果： p  > 
 
 
 \ N + ---- + ------------ + -------- + \ N |  ID |  ID_PRODUCT | 颜色| 
 + ---- + ------------ + -------- + 
 |  2 |  1 | 黄色| 
 |  5 |  3 | 蓝色| 
 |  7 |  4 |  YELLOW | 
 + ---- + ------------ + -------- + 
  pre> 
 
 此示例的原始SQL：   p> 
 
 
 
创建表`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`color` VALUES（1,1，'BLUE'），（2,1）  ， '黄色'），（3,2， '红'），（4,2， 'PINK'），（5,3， '蓝'），（6,3， '灰色'），（7,4  ，'YELLOW'）; 
 
INSERT INTO`products`VALUES（1，'Prod A'），（2，'Prod B'），（3，'Prod C'），（4，'Prod D'）  ; 
  pre> 
  div>

答

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;

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

相关推荐