CREATE TABLE EMPLOYEE (
empId INTEGER Not NULL,
id1 INTEGER NOT NULL,
id2 INTEGER NOT NULL,
id3 INTEGER NOT NULL
);
-- insert
INSERT INTO EMPLOYEE VALUES (0001, 1, 10,100);
INSERT INTO EMPLOYEE VALUES (0001, 2, 10,100);
INSERT INTO EMPLOYEE VALUES (0002, 3, 10,100);
INSERT INTO EMPLOYEE VALUES (0002, 3, 11,100);
INSERT INTO EMPLOYEE VALUES (0003, 1, 12,101);
INSERT INTO EMPLOYEE VALUES (0003, 1, 12,102);
-- fetch
select * from EMPLOYEE where (empId,(id1)) in (SELECT empId,min(id1) FROM EMPLOYEE WHERE 1=1 group by empId);
这个查询给了我:
empId id1 id2 id3
1 1 10 100
2 3 10 100
2 3 11 100
3 1 12 101
3 1 12 102
我需要的是:
empId id1 id2 id3
1 1 10 100
2 3 11 100
3 1 12 102
所以,我在寻找id2的最小值,如果id1相等,如果id2和id1相等。如何使用查询实现这一点?
小提琴(更新(
注意:第一个查询需要MySQL 8.0或更高版本。对于较旧的版本,需要稍微不同的解决方案。请参阅第二个查询以支持旧版本。
我认为您的预期结果是错误的,因为当empId=3的id1、id2相同时,101是最小id3值,而不是102。但是,下面应该使用ROW_NUMBER
窗口函数来执行您的要求。
对于每个empId,根据(id1,id2,id3(是加权最小的元组,从左到右为每一行分配一个行号,然后只选择每个分区中的第一个行号(rn = 1
(。
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY empId ORDER BY id1, id2, id3) AS rn
FROM EMPLOYEE
)
SELECT * FROM cte WHERE rn = 1
;
对于旧版本(这不会在结果中产生rn
列(:
SELECT e1.*
FROM EMPLOYEE AS e1
LEFT JOIN EMPLOYEE AS e2
ON e1.empId = e2.empId
AND (e1.id1, e1.id2, e1.id3) > (e2.id1, e2.id2, e2.id3)
WHERE e2.empId IS NULL
;
结果:
empId | id1 | id2 | id3rn | |
---|---|---|---|---|
1 | 1 | 10 | 100 | <1>|
2 | 3 | 10 | 100 | 1 |
3 | 1 | 12 | 101 | <1>