MySql条件查询按照当天、当月、当周

MySql常见查询

--根据kpi表查询所有信息
SELECT * FROM `kpi`
--批量插入 数据库mybase里面的kpi数据库
INSERT INTO `mybase`.`kpi` ( `kpi_name`, `input_date`, `bga_name`, `kpi_value`, `level`, `kpi_cn_name`) 
VALUES 
('XXXXXXX', '2023-02-02', 'ME', '20', 'XXXXXXX', 'XXXXXXX'),
('XXXXXXX', '2023-02-02', 'ME', '20', 'XXXXXXX', 'XXXXXXX'),
('XXXXXXX', '2023-02-02', 'ME', '20', 'department', 'XXXXXXX');

--按照当月日期查询  查询当月的target的总值，input_date为数据库字段录入数据日期
select sum(target) from kpi where kpi_name = 'Output' and date_format(input_date,'%Y-%m') = date_format(now(),'%Y-%m');

--查询当天数据  用的date_format函数
--kpi_value ：要查询的字段值、kpi:表名、kpi_name：字段、bga_name ：字段、input_date：录入数据的日期
select kpi_value from kpi where kpi_name = 'XXXXXX' and bga_name = 'ME' and YEARWEEK(date_format(input_date,'%Y-%m-%d')) = YEARWEEK(now());

--某一天的数值求和
select sum(kpi_value) from kpi where kpi_name = 'XXXXXX' and input_date = '2023-2-5';


--当周数据
select kpi_value from kpi where YEARWEEK(date_format(input_date,'%Y-%m-%d')) = YEARWEEK(now());