项目中遇到的sql问题记录

有一张表，表结构及数据如下：

INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve001', '2021-02-18 00:00:00', 'tableA', '2024-03-18'); INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve002', '2021-02-18 00:05:00', 'tableB', '2024-03-18'); INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve002', '2021-02-18 00:05:00', 'tableB', '2024-03-18'); INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve003', '2022-05-18 00:05:00', 'tableC', '2024-03-18'); INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve003', '2021-03-18 00:05:00', 'tableC', '2024-03-18'); INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve003', '', 'tableC', '2024-03-18'); INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve004', '', 'tableB', '2024-03-18'); INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve004', '', 'tableA', '2024-03-18'); INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve005', '2023-02-18 00:05:00', 'tableD', '2024-03-18'); INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve005', '2023-03-18 00:05:00', 'tableD', '2024-03-18'); INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve006', '', 'tableC', '2024-03-18'); INSERT INTO `test`.`test_approve`(`approve_no`, `tra_date`, `tablename`, `part_dt`) VALUES ('approve006', '2024-03-18 00:05:00', 'tableB', '2024-03-18');

根据表名依次排序 SELECT approve_no, tra_date, tablename, part_dt FROM test_approve WHERE tablename IN ('tableC', 'tableD', 'tableB', 'tableA') ORDER BY CASE tablename WHEN 'tableC' THEN 1 WHEN 'tableD' THEN 2 WHEN 'tableB' THEN 3 WHEN 'tableA' THEN 4 ELSE 5 -- 处理其他表名 END;

tra_date 不为空（‘’） SELECT approve_no, tra_date, tablename, part_dt FROM test_approve WHERE tablename IN ('tableC', 'tableD', 'tableB', 'tableA') AND tra_date <>'' ORDER BY CASE tablename WHEN 'tableC' THEN 1 WHEN 'tableD' THEN 2 WHEN 'tableB' THEN 3 WHEN 'tableA' THEN 4 ELSE 5 -- 处理其他表名 END;

遇到相同的approve_no，取tra_date最大的 SELECT approve_no, tra_date, tablename, part_dt FROM ( SELECT approve_no, tra_date, tablename, part_dt, ROW_NUMBER() OVER (PARTITION BY approve_no ORDER BY tra_date DESC) AS rn FROM test_approve WHERE tablename IN ('tableC', 'tableD', 'tableB', 'tableA') -- AND tra_date IS NOT NULL --AND tra_date <>'' ) sub WHERE rn = 1 ORDER BY CASE tablename WHEN 'tableC' THEN 1 WHEN 'tableD' THEN 2 WHEN 'tableB' THEN 3 WHEN 'tableA' THEN 4 ELSE 5 -- 处理其他表名 END; 相同的approve_no，优先取tableC，tableD，tableB，tableA不为空的tra_date，按表名顺序取，取到了则返回该条数据 SELECT t.approve_no, t.tra_date, t.tablename, t.part_dt FROM ( SELECT approve_no, tra_date, tablename, part_dt, ROW_NUMBER() OVER (PARTITION BY approve_no ORDER BY CASE WHEN tablename = 'tableC' THEN 1 WHEN tablename = 'tableD' THEN 2 WHEN tablename = 'tableB' THEN 3 WHEN tablename = 'tableA' THEN 4 ELSE 5 END, CASE WHEN tra_date <> '' THEN 0 ELSE 1 END, tra_date DESC) AS row_number FROM test_approve ) AS t WHERE t.row_number = 1;