我想要创建一个忽略重复的SELECT。重复项只应由某些列检测,而我仍然希望选择一行中的所有列。
示例:
CREATE TABLE employee (
id integer,
firstname varchar(100),
lastname varchar(100),
country varchar(100),
salary interger,
//many more fields...
);
select * from employee GROUP BY firstname, lastname, country;当然,这是无效的sql,但它显示了我的意图:
如果(姓氏、姓氏、国家)的任何组合形成了一个重复的键,那么我只想在重复的行中选择一个(),但是要选择它的所有列。
最好是从重复项中选择salary列中值最高的行。
我用的是mysql 8
发布于 2021-08-10 10:24:59
您可以使用ROW_NUMBER()来完成此操作。实际上,您为分组发布的内容将成为您的分区,然后您可以选择如何对每个组进行排序(在下面的salary中):
select *
from (select *,
row_number() over(partition by firstname, lastname, country
order by salary desc) AS rownum
from employee) AS e
where e.rownum = 1;发布于 2021-08-10 10:23:17
您可以使用row_number()来选择名字、姓氏、国家的唯一组合:
select t.*
from (select t.*,
row_number() over (partition by firstname, lastname, country order by id) as seqnum
from t
) t
where seqnum = 1;如果只希望有重复的一行,则包括一个count()。
select t.*
from (select t.*,
row_number() over (partition by firstname, lastname, country order by id) as seqnum,
count(*) over (partition by firstname, lastname, country) as cnt
from t
) t
where seqnum = 1 and cnt > 1;https://stackoverflow.com/questions/68725015
复制相似问题