以mysql实现按星期打折

折扣测试表和数据

CREATE TABLE `discount`
(
    `id`           int          NOT NULL AUTO_INCREMENT,
    `goods_number` varchar(100) NOT NULL,
    `rate`         int          NOT NULL DEFAULT '100',
    `week`         int          NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into discount (goods_number, rate, week)
values ('100001', 100, 1),
       ('100001', 90, 2),
       ('100001', 80, 3),
       ('100001', 70, 4),
       ('100001', 60, 5),
       ('100001', 50, 6),
       ('100001', 30, 7);

商品测试表和数据

CREATE TABLE `goods`
(
    `id`           int            NOT NULL AUTO_INCREMENT,
    `goods_number` varchar(100)   NOT NULL,
    `goods_name`   varchar(100)   NOT NULL,
    `price`        decimal(10, 2) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into goods (goods_number, goods_name, price)
values ('100001', '九转大肠', 100),
       ('100002', '七转大肠', 200);

计算当前日期商品折扣

这里使用了 ifnull(value,default) value 为 null 则为 default
这里使用了CASE 表达式1 WHEN VALUE1 THEN VALUE2 ELSE VALUE3 END 如果 表达式1的值 = VALUE1 则 输出 VALUE2,否则输出 VALUE3
DAYOFWEEK(date) 返回周几，1代表周日 2代表周一，以此类推7代表周六

select a.goods_name                                                          as '商品名称',
       a.goods_number                                                        as '商品编码',
       case DAYOFWEEK(now()) - 1 when 0 then 7 else DAYOFWEEK(now()) - 1 end as '星期',
       ifnull(d.rate, 100) / 100                                             as '折扣率',
       ifnull(d.rate, 100) / 100 * a.price                                   as '价格'
from goods as a
         left join discount d on a.goods_number = d.goods_number and
                                 d.week = case DAYOFWEEK(now()) - 1 when 0 then 7 else DAYOFWEEK(now()) - 1 end;