项目中遇到的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 tablenameWHEN 'tableC' THEN 1WHEN 'tableD' THEN 2WHEN 'tableB' THEN 3WHEN 'tableA' THEN 4ELSE 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 tablenameWHEN 'tableC' THEN 1WHEN 'tableD' THEN 2WHEN 'tableB' THEN 3WHEN 'tableA' THEN 4ELSE 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 rnFROM test_approveWHERE tablename IN ('tableC', 'tableD', 'tableB', 'tableA')-- AND tra_date IS NOT NULL--AND tra_date <>''
) sub
WHERE rn = 1
ORDER BY CASE tablenameWHEN 'tableC' THEN 1WHEN 'tableD' THEN 2WHEN 'tableB' THEN 3WHEN 'tableA' THEN 4ELSE 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 1WHEN tablename = 'tableD' THEN 2WHEN tablename = 'tableB' THEN 3WHEN tablename = 'tableA' THEN 4ELSE 5END,CASE WHEN tra_date <> '' THEN 0 ELSE 1 END,tra_date DESC) AS row_numberFROM test_approve
) AS t
WHERE t.row_number = 1;