MySQL-练习-数据汇总-CASE WHEN

文章目录

• 一. 数据介绍
• 二. 数据汇总
• 三. 使用CASE WHEN 和 GROUP BY将数据分组
• • 1. 使用CASE WHEN自定义分组
  • • 练习15
    • 练习16
  • 2. CASE WHEN中ELSE的使用
  • • 练习17
    • 练习18
    • 练习19
  • 3. 在GROUP BY中使用CASE WHEN
  • • 练习20
    • 练习21
  • 4. CASE WHEN 和 COUNT
  • • 练习22
    • 练习23
  • 5. GROUP BY 和 CASE WHEN组合使用
  • • 练习24
  • 6. SUM中使用CASE WHEN
  • • 练习25
  • 7. SUM中使用CASE WHEN进行复杂计算
  • • 练习27
  • 8. 小结
  • • 练习28
    • 练习29
    • 练习30
• 四. 窗口函数

一. 数据介绍

二. 数据汇总

三. 使用CASE WHEN 和 GROUP BY将数据分组

1. 使用CASE WHEN自定义分组

• 需求：我们要在报表中显示每种产品的库存量，但我们不想简单地将“ units_in_stock”列放在报表中。报表中只需要一个总体级别，例如低，高：

  SELECT
    product_id,
    product_name,
    units_in_stock,
    CASE
      WHEN units_in_stock > 100 THEN 'high'
      WHEN units_in_stock > 50 THEN 'moderate'
      WHEN units_in_stock > 0 THEN 'low'
      WHEN units_in_stock = 0 THEN 'none'
    END AS availability
  FROM products;
  

• 上面的SQL查询结果中，我们创建了一个新列availability， 通过 CASE WHEN 语句来对这一列赋值

• CASE WHEN 语法

• 上面的查询中，通过 units_in_stock 列的值来判断库存的可用性

  • 库存大于100 的可用性为高(high)
  • 50到100的可用性为中等(moderate)
  • 小于50的为低(low)
  • 零库存 为 (none)

练习15

运行上面的SQL，比较units_in_stock 和 availability两列的结果

product_id

product_name

units_in_stock

availability

1

Chai

39

low

2

Chang

17

low

3

Aniseed Syrup

13

low

4

Chef Anton’s Cajun Seasoning

53

moderate

5

Chef Anton’s Gumbo Mix

0

none

6

Grandma’s Boysenberry Spread

120

high

7

Uncle Bob’s Organic Dried Pears

15

low

8

Northwoods Cranberry Sauce

6

low

9

Mishi Kobe Niku

29

low

10

Ikura

31

low

11

Queso Cabrales

22

low

12

Queso Manchego La Pastora

86

moderate

13

Konbu

24

low

14

Tofu

35

low

15

Genen Shouyu

39

low

16

Pavlova

29

low

17

Alice Mutton

0

none

18

Carnarvon Tigers

42

low

19

Teatime Chocolate Biscuits

25

low

20

Sir Rodney’s Marmalade

40

low

练习16

• 需求： 创建一个报表，统计员工的经验水平

• 显示字段：first_name, last_name, hire_date, 和 experience

• 经验字段（experience ）：

  • 'junior' 2014年1月1日以后雇用的员工
  • 'middle' 在2013年1月1日之后至2014年1月1日之前雇用的员工
  • 'senior' 2013年1月1日或之前雇用的员工

  SELECT
  first_name,
  last_name,
  hire_date,
  CASE
  WHEN hire_date > ‘2014-01-01’ THEN ‘junior’
  WHEN hire_date > ‘2013-01-01’ and hire_date < ‘2014-01-01’ THEN ‘middle’
  WHEN hire_date <= ‘2013-01-01’ THEN ‘senior’
  END AS experience
  FROM employees;

first_name

last_name

hire_date

experience

Nancy

Davolio

2012-05-01

senior

Andrew

Fuller

2012-08-14

senior

John

Smith

2012-04-01

senior

Margaret

Peacock

2013-05-03

middle

Steven

Buchanan

2013-10-17

middle

Michael

Suyama

2013-10-17

middle

Robert

King

2014-01-02

junior

Laura

Callahan

2014-03-05

junior

Anne

Dodsworth

2014-11-15

junior

John

Smith

2017-03-21

junior

2. CASE WHEN中ELSE的使用

• 我们的商店要针对北美地区的用户做促销活动：任何运送到北美地区（美国，加拿大) 的包裹免运费。

• 创建报表，查询订单编号为10720~10730 活动后的运费价格

  SELECT 
    order_id,
    customer_id,
    ship_country,
    CASE
      WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN 0.0
    END AS shipping_cost
  FROM orders
  WHERE order_id BETWEEN 10720 AND 10730;
  

• 上面的SQL中，只定义了美国和加拿大的运费，并没有处理其他目的地的运费信息

练习17

• 运行上面的SQL 观察 ship_country 和 shipping_cost 列，除了美国和加拿大之外，其他行的 shipping_cost 的值为NULL

order_id

customer_id

ship_country

shipping_cost

10720

QUEDE

Brazil

null

10721

QUICK

Germany

null

10722

SAVEA

USA

0.0

10723

WHITC

USA

0.0

10724

MEREP

Canada

0.0

10725

FAMIA

Brazil

null

10726

EASTC

UK

null

10727

REGGC

Italy

null

10728

QUEEN

Brazil

null

10729

LINOD

Venezuela

null

10730

BONAP

France

null

• 在上面的案例中，除了北美地区的以外的订单，运费统计为NULL, 如果将其他地区的运费设置为10美元，那么可以用如下方式处理：

  SELECT 
    order_id,
    customer_id,
    ship_country,
    CASE
      WHEN ship_country = 'USA' OR ship_country = 'Canada' THEN 0.0
      ELSE 10.0
    END AS shipping_cost
  FROM orders
  WHERE order_id BETWEEN 10720 AND 10730;
  

• 我们在CASE WHEN结构中添加了ELSE

• 如果不满足其他条件，则执行ELSE。 因此，所有其他国家/地区的 shipping_cost都将变为“ 10.0”，而不是NULL。

练习18

• 需求：创建客户基本信息报表
• 包含字段：
  • 客户id customer_id
  • 公司名字 company_name
  • 所在国家 country
  • 使用语言language
• 使用语言language 的取值按如下规则

  • Germany, Switzerland, and Austria 语言为德语 'German'

  • UK, Canada, the USA, and Ireland 语言为英语 'English'

  • 其他所有国家 'Other'

    SELECT 
      customer_id,
      company_name,
      country,
      CASE
        WHEN country IN ('Germany', 'Switzerland', 'Austria') THEN 'German'