mysql - 使用sql比较列中的值

Question

我有一个包含三列 Member、id 和 DOB 的表。我想为每个唯一成员分配一个 id。如果为一名成员标记了多个 id，那么我必须分配具有更多重复性的 id。如果出现平局，那么我必须为最近的 DOB 分配 id。

4000  8569 11/11/1993
4111  9653 12/11/1993
4000  8569 12/12/1993
5000  5632 01/01/1993
4000  6932 31/12/1993
4111  6987 06/11/1993
5001  4356 01/01/1993

在上面，成员的 5000 和 5001 被标记为单个 ID。所以我应该为该成员获得相同的 ID。而对于成员 4000，我有 3 个 ID - 2 个相同的 ID (8569) 和一个不同的 ID (6987)。在这里，我应该将 8569 标记给这 4000 名成员。对于 4111 成员，我有两个不同的 ID(9653 和 6987)。所以我会看到该成员最近的 DOB。因此，对于 4111 成员，我将为其标记 9653。

输出应该是这样的:

4000 8569
4111 9653
5000 5632
5001 4356

我试过很多。但我无法得到确切的答案。请帮我解决这个问题。提前致谢。

Answer 1

您可以使用 window functions in t-sql 执行此操作:

create table #t (
    Member int,
    id int,
    DOB date
);

insert into #t
values  (4000, 8569, '1993-11-11'),
        (4111, 9653, '1993-11-12'),
        (4000, 8569, '1993-12-12'),
        (5000, 5632, '1993-01-01'),
        (4000, 6932, '1993-12-31'),
        (4111, 6987, '1993-11-06'),
        (5001, 4356, '1993-01-01');

with cte as
(
    select *, count(id) over (partition by member, id) cnt from #t
),
cte2 as
(
    select *, row_number() over (partition by member order by cnt desc, dob desc) rn from cte
)
select member, id from cte2 where rn = 1;

drop table #t;