mysql - 从重复的 id 中仅获取一个 id(均具有不同的值)并仅使用一个值永久更新唯一 id(应用条件)

Question

我似乎无法解决这个问题。我需要在表中永久更新此内容。我需要在这里应用 2 个条件 -

(1) 当同一个 id 同时具有“学士”和“硕士”值时，我只需拥有该 id 一次，并且它应该包含“学士”学位。

(2) 当同一个 id 具有“学士”、“硕士”和“博士”值时，我只需要拥有该 id 一次，并且它应该包含“硕士”学位。

id         degree 
1           bachelor
2           master
3           bachelor
1           master
2           bachelor 
2           phd 

我想要这样的结果 -

1        bachelor
2        master 
3        bachelor 

希望它在表中永久更新，以便我可以将其连接到其他表。

Answer 1

好的，所以首先我尝试按照您所描述的操作，然后我做到了

drop table test7;

create table test7 (
id_num number,
education varchar(25)
);

insert into test7 values(1,'bachelor');
insert into test7 values(2,'masters');
insert into test7 values(3,'bachelor');
insert into test7 values(1,'masters');
insert into test7 values(2,'bachelor');
insert into test7 values(2,'phd');

-- this finds those id numbers that have duplicates
--select count(id_num),id_num from test7 group by id_num having count(id_num) > 1;

-- this shows the duplicate ids, their id number, and their education
--select c.multi,c.id_num,t.education from (select count(id_num) multi,id_num from test7 group by id_num having count(id_num) > 1) c left join test7 t on c.id_num=t.id_num;

-- this finds the distinct id number that has 2 duplicates
--select distinct id_num from (select c.multi,c.id_num,t.education from (select count(id_num) multi,id_num from test7 group by id_num having count(id_num) > 1) c left join test7 t on c.id_num=t.id_num) where multi = 2;

-- when there are two records with the same id, get rid of the masters one
delete from test7 where id_num = (select distinct id_num from (select c.multi,c.id_num,t.education from (select count(id_num) multi,id_num from test7 group by id_num having count(id_num) > 1) c left join test7 t on c.id_num=t.id_num) where multi = 2) and education = 'masters'; 

-- when there are 3 records with the same id, get rid of bachelor and phd
delete from test7 where id_num = (select distinct id_num from (select c.multi,c.id_num,t.education from (select count(id_num) multi,id_num from test7 group by id_num having count(id_num) > 1) c left join test7 t on c.id_num=t.id_num) where multi = 3) and education = 'bachelor' or education = 'phd'; 

    select * from test7;

    ID_NUM EDUCATION               
---------- -------------------------
         1 bachelor                 
         2 masters  
         3 bachelor     

这适用于您所描述的内容，但不允许有太多变化。

但是，通常当您想要根据 id 数字连接表时，您需要不同的 id/值对，因此我添加了一个也适用于该情况的解决方案...

drop table temp;
drop table test6;
drop sequence temp_id_seq;


create table test6 (
id_num number,
education varchar(25)
);

insert into test6 values(1,'bachelor');
insert into test6 values(2,'master');
insert into test6 values(3,'bachelor');
insert into test6 values(1,'master');
insert into test6 values(2,'bachelor');
insert into test6 values(2,'phd');

create table temp (
temp_id number,
education varchar(25)
);

create sequence temp_id_seq
minvalue 1
maxvalue 100000
start with 1
increment by 1
nocycle;

create or replace trigger created_temp_id
before insert on temp
for each row
begin
:new.temp_id := temp_id_seq.nextval;
end;
/

insert into temp(education) select distinct education from test6;

select * from temp;

delete from test6;

insert into test6 select * from temp;

select * from test6;

-- which results in this:

    ID_NUM EDUCATION               
---------- -------------------------
         1 master                   
         2 phd                      
         3 bachelor     

希望这对您有帮助:)