case.md

# 透视表

Goods 表结构如下

```mysql
create table goods(
                      id int primary key auto_increment,
                      category_id int,
                      category varchar(64),
                      name varchar(256),
                      price decimal(12, 4),
                      stock int,
                      upper_time timestamp
)
```

Joe 想要做一个报表，只需要显示商品名和价格分级，其中不足10元的是 cheap, 超过1000的是expensive，其它的是 
normal，这个查询应该怎么写？

<hr/>

点击进入[MySQL实战练习环境](https://mydev.csdn.net/product/pod/new?image=cimg-centos7-skilltreemysql&connect=auto&create=auto&utm_source=skill)。

* `show databases` 列出所有数据库
* `show tables` 列出所有表

## 答案

```mysql
select name,
       case 
           when price < 10 then 'cheap'
           when price > 1000 then 'expensive'
           else 'normal'
       end as level
from goods;
```

## 选项

### A

```mysql
select name,
       case price
           when  < 10 then 'cheap'
           when  > 1000 then 'expensive'
           else 'normal'
           end as level
from goods;
```

### B

```mysql
select name,
       case
           when price < 10  'cheap'
           when price > 1000 'expensive'
           else 'normal'
           end as level
from goods;
```

### C

```mysql
select name,
       case
           when price < 10 then 'cheap'
           when price > 1000 then 'expensive'
           case _ 'normal'
           end as level
from goods;
```

### C

```mysql
select name,
       case
           when price < 10 then 'cheap'
           when price > 1000 then 'expensive'
           case _ 'normal'
           end as level
from goods;
```