DROP TABLE record_batch;
CREATE TABLE record_batch
(
id varchar(16) NOT NULL,
supplier_count int NOT NULL,
employee_count int NOT NULL,
start_date date DEFAULT NULL,
end_date date DEFAULT NULL
);
DROP TABLE record_summary;
CREATE TABLE record_summary
(
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
batch_id varchar(16) NOT NULL,
supplier_id varchar(16) NOT NULL,
employee_id varchar(16) NOT NULL,
work_days float NOT NULL,
over_times float NOT NULL
);
DROP TABLE record;
CREATE TABLE record
(
id varchar(16) NOT NULL,
batch_id varchar(16) NOT NULL,
supplier_id varchar(16) NOT NULL,
employee_id varchar(16) NOT NULL,
record_date date NOT NULL,
record_days float NOT NULL,
over_times float NOT NULL,
create_time datetime DEFAULT now()
);
INSERT INTO record VALUES ('record_1', 'batch_1', 'supplier_1', 'employee_1', '2021/10/10', 1, 1, now());
INSERT INTO record VALUES ('record_2', 'batch_1', 'supplier_1', 'employee_1', '2021/10/11', 1, 1, now());
INSERT INTO record VALUES ('record_3', 'batch_1', 'supplier_1', 'employee_1', '2021/10/12', 1, 1, now());
INSERT INTO record VALUES ('record_4', 'batch_1', 'supplier_1', 'employee_1', '2021/10/13', 1, 1, now());
INSERT INTO record VALUES ('record_5', 'batch_1', 'supplier_1', 'employee_1', '2021/10/14', 1, 1, now());
INSERT INTO record VALUES ('record_6', 'batch_1', 'supplier_1', 'employee_1', '2021/10/15', 1, 1, now());
INSERT INTO record VALUES ('record_7', 'batch_1', 'supplier_1', 'employee_1', '2021/10/16', 0, 0, now());
INSERT INTO record VALUES ('record_8', 'batch_1', 'supplier_1', 'employee_2', '2021/10/10', 1, 1, now());
INSERT INTO record VALUES ('record_9', 'batch_1', 'supplier_1', 'employee_2', '2021/10/11', 1, 1, now());
INSERT INTO record VALUES ('record_10', 'batch_1', 'supplier_1', 'employee_2', '2021/10/12', 1, 1, now());
INSERT INTO record VALUES ('record_11', 'batch_1', 'supplier_1', 'employee_2', '2021/10/13', 1, 1, now());
INSERT INTO record VALUES ('record_12', 'batch_1', 'supplier_1', 'employee_2', '2021/10/14', 1, 1, now());
INSERT INTO record VALUES ('record_13', 'batch_1', 'supplier_1', 'employee_2', '2021/10/15', 0, 0, now());
INSERT INTO record VALUES ('record_14', 'batch_1', 'supplier_1', 'employee_2', '2021/10/16', 0, 0, now());
INSERT INTO record VALUES ('record_15', 'batch_1', 'supplier_1', 'employee_3', '2021/10/10', 1, 1, now());
INSERT INTO record VALUES ('record_16', 'batch_1', 'supplier_1', 'employee_3', '2021/10/11', 1, 1, now());
INSERT INTO record VALUES ('record_17', 'batch_1', 'supplier_1', 'employee_3', '2021/10/12', 1, 1, now());
INSERT INTO record VALUES ('record_18', 'batch_1', 'supplier_1', 'employee_3', '2021/10/13', 1, 1, now());
INSERT INTO record VALUES ('record_19', 'batch_1', 'supplier_1', 'employee_3', '2021/10/14', 1, 1, now());
INSERT INTO record VALUES ('record_20', 'batch_1', 'supplier_1', 'employee_3', '2021/10/15', 0, 0, now());
INSERT INTO record VALUES ('record_21', 'batch_1', 'supplier_1', 'employee_3', '2021/10/16', 0, 0, now());
INSERT INTO record VALUES ('record_22', 'batch_1', 'supplier_2', 'employee_4', '2021/10/10', 1, 1, now());
INSERT INTO record VALUES ('record_23', 'batch_1', 'supplier_2', 'employee_4', '2021/10/11', 1, 1, now());
INSERT INTO record VALUES ('record_24', 'batch_1', 'supplier_2', 'employee_4', '2021/10/12', 1, 1, now());
INSERT INTO record VALUES ('record_25', 'batch_1', 'supplier_2', 'employee_4', '2021/10/13', 1, 1, now());
INSERT INTO record VALUES ('record_26', 'batch_1', 'supplier_2', 'employee_4', '2021/10/14', 1, 1, now());
INSERT INTO record VALUES ('record_27', 'batch_1', 'supplier_2', 'employee_4', '2021/10/15', 1, 0, now());
INSERT INTO record VALUES ('record_28', 'batch_1', 'supplier_2', 'employee_4', '2021/10/16', 1, 0, now());
INSERT INTO record VALUES ('record_29', 'batch_1', 'supplier_2', 'employee_5', '2021/10/10', 1, 1, now());
INSERT INTO record VALUES ('record_30', 'batch_1', 'supplier_2', 'employee_5', '2021/10/11', 1, 1, now());
INSERT INTO record VALUES ('record_31', 'batch_1', 'supplier_2', 'employee_5', '2021/10/12', 1, 1, now());
INSERT INTO record VALUES ('record_32', 'batch_1', 'supplier_2', 'employee_5', '2021/10/13', 1, 1, now());
INSERT INTO record VALUES ('record_33', 'batch_1', 'supplier_2', 'employee_5', '2021/10/14', 1, 1, now());
INSERT INTO record VALUES ('record_34', 'batch_1', 'supplier_2', 'employee_5', '2021/10/15', 1, 0, now());
INSERT INTO record VALUES ('record_35', 'batch_1', 'supplier_2', 'employee_5', '2021/10/16', 1, 0, now());
INSERT INTO record VALUES ('record_36', 'batch_2', 'supplier_1', 'employee_1', '2021/10/17', 1, 1, now());
INSERT INTO record VALUES ('record_37', 'batch_2', 'supplier_1', 'employee_1', '2021/10/18', 1, 1, now());
INSERT INTO record VALUES ('record_38', 'batch_2', 'supplier_1', 'employee_1', '2021/10/19', 1, 1, now());
INSERT INTO record VALUES ('record_39', 'batch_2', 'supplier_1', 'employee_1', '2021/10/20', 1, 1, now());
INSERT INTO record VALUES ('record_40', 'batch_2', 'supplier_1', 'employee_1', '2021/10/21', 1, 1, now());
INSERT INTO record VALUES ('record_41', 'batch_2', 'supplier_1', 'employee_1', '2021/10/22', 1, 0, now());
INSERT INTO record VALUES ('record_42', 'batch_2', 'supplier_1', 'employee_1', '2021/10/23', 1, 0, now());
INSERT INTO record VALUES ('record_43', 'batch_2', 'supplier_2', 'employee_5', '2021/10/17', 1, 1, now());
INSERT INTO record VALUES ('record_44', 'batch_2', 'supplier_2', 'employee_5', '2021/10/18', 1, 1, now());
INSERT INTO record VALUES ('record_45', 'batch_2', 'supplier_2', 'employee_5', '2021/10/19', 1, 1, now());
INSERT INTO record VALUES ('record_46', 'batch_2', 'supplier_2', 'employee_5', '2021/10/20', 1, 1, now());
INSERT INTO record VALUES ('record_47', 'batch_2', 'supplier_2', 'employee_5', '2021/10/21', 1, 1, now());
INSERT INTO record VALUES ('record_48', 'batch_2', 'supplier_2', 'employee_5', '2021/10/22', 0, 0, now());
INSERT INTO record VALUES ('record_49', 'batch_2', 'supplier_2', 'employee_5', '2021/10/23', 1, 0, now());
INSERT INTO record VALUES ('record_50', 'batch_3', 'supplier_2', 'employee_4', '2021/10/17', 1, 1, now());
INSERT INTO record VALUES ('record_51', 'batch_3', 'supplier_2', 'employee_4', '2021/10/18', 1, 1, now());
INSERT INTO record VALUES ('record_52', 'batch_3', 'supplier_2', 'employee_4', '2021/10/19', 1, 1, now());
INSERT INTO record VALUES ('record_53', 'batch_3', 'supplier_2', 'employee_4', '2021/10/20', 1, 1, now());
INSERT INTO record VALUES ('record_54', 'batch_3', 'supplier_2', 'employee_4', '2021/10/21', 1, 1, now());
INSERT INTO record VALUES ('record_55', 'batch_3', 'supplier_2', 'employee_4', '2021/10/22', 0, 0, now());
INSERT INTO record VALUES ('record_56', 'batch_3', 'supplier_2', 'employee_4', '2021/10/23', 1, 0, now());
INSERT INTO record VALUES ('record_57', 'batch_3', 'supplier_3', 'employee_6', '2021/10/17', 1, 1, now());
INSERT INTO record VALUES ('record_58', 'batch_3', 'supplier_3', 'employee_6', '2021/10/18', 1, 1, now());
INSERT INTO record VALUES ('record_59', 'batch_3', 'supplier_3', 'employee_6', '2021/10/19', 1, 1, now());
INSERT INTO record VALUES ('record_60', 'batch_3', 'supplier_3', 'employee_6', '2021/10/20', 1, 1, now());
INSERT INTO record VALUES ('record_61', 'batch_3', 'supplier_3', 'employee_6', '2021/10/21', 1, 1, now());
INSERT INTO record VALUES ('record_62', 'batch_3', 'supplier_3', 'employee_6', '2021/10/22', 0, 0, now());
INSERT INTO record VALUES ('record_62', 'batch_3', 'supplier_3', 'employee_6', '2021/10/23', 1, 0, now());
INSERT INTO record VALUES ('record_63', 'batch_4', 'supplier_4', 'employee_7', '2021/10/17', 1, 1, now());
INSERT INTO record VALUES ('record_64', 'batch_4', 'supplier_4', 'employee_7', '2021/10/18', 1, 1, now());
INSERT INTO record VALUES ('record_65', 'batch_4', 'supplier_4', 'employee_7', '2021/10/19', 1, 1, now());
INSERT INTO record VALUES ('record_66', 'batch_4', 'supplier_4', 'employee_7', '2021/10/20', 1, 1, now());
INSERT INTO record VALUES ('record_67', 'batch_4', 'supplier_4', 'employee_7', '2021/10/21', 1, 1, now());
INSERT INTO record VALUES ('record_68', 'batch_4', 'supplier_4', 'employee_7', '2021/10/22', 0, 0, now());
INSERT INTO record VALUES ('record_69', 'batch_4', 'supplier_4', 'employee_7', '2021/10/23', 1, 0, now());
INSERT INTO record_batch (id, supplier_count, employee_count)
SELECT
batch_id,
COUNT(DISTINCT supplier_id),
COUNT(DISTINCT employee_id)
FROM
record
GROUP BY
batch_id;
INSERT INTO record_summary (batch_id, supplier_id, employee_id, work_days, over_times)
SELECT
batch_id,
supplier_id,
employee_id,
SUM(record_days),
SUM(over_times)
FROM
record
GROUP BY
employee_id, batch_id;
INSERT INTO record VALUES ('record_70', 'batch_5', 'supplier_4', 'employee_7', '2021/10/10', 1, 1, now());
INSERT INTO record VALUES ('record_71', 'batch_5', 'supplier_4', 'employee_7', '2021/10/11', 1, 1, now());
INSERT INTO record VALUES ('record_72', 'batch_5', 'supplier_4', 'employee_7', '2021/10/12', 1, 1, now());
INSERT INTO record VALUES ('record_73', 'batch_5', 'supplier_4', 'employee_7', '2021/10/13', 1, 1, now());
INSERT INTO record VALUES ('record_74', 'batch_5', 'supplier_4', 'employee_7', '2021/10/14', 1, 1, now());
INSERT INTO record VALUES ('record_75', 'batch_5', 'supplier_4', 'employee_7', '2021/10/15', 0, 0, now());
INSERT INTO record VALUES ('record_76', 'batch_5', 'supplier_4', 'employee_7', '2021/10/16', 1, 0, now());
INSERT INTO record VALUES ('record_77', 'batch_5', 'supplier_4', 'employee_1', '2021/10/09', 1, 0, now());
SELECT
record.id,
record.employee_id,
record.record_date,
record.batch_id,
record.record_date,
batch.supplier_count,
batch.employee_count
FROM
record
INNER JOIN record_batch batch ON record.batch_id = batch.id
WHERE
record.employee_id = 'employee_7'
AND record.record_date <= '2021-10-10'
AND record.record_date >= '2021-10-08';
SELECT
record.id,
record.employee_id,
record.record_date,
record.batch_id,
record.record_date,
record.create_time,
batch.supplier_count,
batch.employee_count
FROM
record
LEFT JOIN record_batch batch ON record.batch_id = batch.id
WHERE
record.employee_id = 'employee_7'
AND record.record_date <= '2021-20-10'
AND record.record_date >= '2021-10-08'
AND record.create_time <= '2021/12/04 22:30:00'
AND batch.id IS NULL ;
|