mysql> create database shopping;    # 創(chuàng)建數(shù)據(jù)庫
mysql> use shopping;    # 使用數(shù)據(jù)庫

mysql> create table orders(
    -> id int(11) primary key not null comment '訂單編號(hào)',
    -> category varchar(255) default null comment '分類',
    -> status varchar(2) default null comment '狀態(tài)(0:創(chuàng)建,1:待付款,2:待發(fā)貨,3:待收貨)',
    -> amount decimal(10,2) default null comment '金額');

mysql> insert into orders values (0, '華為', '0', 5500.00);
mysql> insert into orders values (1, '小米', '2', 3000.00);
mysql> insert into orders values (2, '華為', '1', 5500.00);
mysql> insert into orders values (3, '蘋果', '2', 5000.00);
mysql> insert into orders values (4, '華為', '1', 5500.00);
mysql> insert into orders values (5, '小米', '2', 3000.00);
mysql> insert into orders values (6, '華為', '0', 5500.00);
mysql> insert into orders values (7, '蘋果', '1', 5000.00);
mysql> insert into orders values (8, '小米', '3', 3000.00);
mysql> insert into orders values (9, '華為', '3', 5500.00);

select * from orders;

mysql> select
    -> count(case when status = '0' then 1 else null end ) as 創(chuàng)建,  
    -> count(case when status = '1' then 1 else null end ) as 待付款,
    -> count(case when status = '2' then 1 else null end ) as 待發(fā)貨,
    -> count(case when status = '3' then 1 else null end ) as 待收貨 
    -> from orders;

mysql> select
    -> count(case when status = '0' then 1 else null end ) as 創(chuàng)建, 
    -> count(case when status = '1' then 1 else null end ) as 待付款,
    -> count(case when status = '2' then 1 else null end ) as 待發(fā)貨,
    -> count(case when status = '3' then 1 else null end ) as 待收貨,
    -> sum(case when status = '0' then ifnull(amount,0) else 0 end) as 創(chuàng)建總金額,
    -> sum(case when status = '1' then ifnull(amount,0) else 0 end) as 待付款總金額, 
    -> sum(case when status = '2' then ifnull(amount,0) else 0 end) as 待發(fā)貨總金額,
    -> sum(case when status = '3' then ifnull(amount,0) else 0 end) as 待收貨總金額  
    -> from orders;

mysql> select category as 分類,
    -> count(case when status = '0' then 1 else null end ) as 創(chuàng)建, 
    -> count(case when status = '1' then 1 else null end ) as 待付款,
    -> count(case when status = '2' then 1 else null end ) as 待發(fā)貨,
    -> count(case when status = '3' then 1 else null end ) as 待收貨,
    -> sum(case when status = '0' then ifnull(amount,0) else 0 end) as 創(chuàng)建總金額,
    -> sum(case when status = '1' then ifnull(amount,0) else 0 end) as 待付款總金額,
    -> sum(case when status = '2' then ifnull(amount,0) else 0 end) as 待發(fā)貨總金額,
    -> sum(case when status = '3' then ifnull(amount,0) else 0 end) as 待收貨總金額  
    -> from orders group by category;

mysql> select * ,
    -> (case when amount<= 3000 then '低' 
    -> when amount<= 5000 then '中'
    -> when amount<= 10000 then '高'
    -> else '未知區(qū)間' end) as 價(jià)格等級(jí) 
    -> from orders;