时间戳

首页 > 解决方案 > MySQL - 在某些列上运行不同的命令

mysql - MySQL - 在某些列上运行不同的命令

问题描述

我正在尝试在以下查询中的四列上运行不同的：

select
  full_records.id,
  full_records.domain_id,
  subdomains.name as subdomain_name,
  types.name as type_name,
  changelog.content as content,
  changelog.changed_on
from full_records
  inner join subdomains on full_records.subdomain_id = subdomains.id
  inner join types on full_records.type_id = types.id
  inner join changelog on full_records.id = changelog.full_record_id
where
  full_records.domain_id = 2
order by changelog.changed_on desc

这将返回以下内容：

SQL 结果

我不确定如何更改查询，以便它只返回这四个字段中唯一的记录。

full_records.domain_id,
subdomains.name as subdomain_name,
types.name as type_name,
changelog.content as content

因此，如果它们在这四个字段中是唯一的，那么第 2、3、4 和 7 行将不会出现在结果中。它基本上是为了识别域记录的最新更改。任何帮助将非常感激。谢谢。

标签： mysqlsql

解决方案

一种非常简单的方法是row_number()：

with cte as (
      select fr.id, fr.domain_id, sd.name as subdomain_name,
             t.name as type_name, cl.content, cl.changed_on
      from full_records fr join
           subdomains sd
           on fr.subdomain_id = sd.id join
           types t
           on fr.type_id = t.id join
           changelog cl
           on fr.id = cl.full_record_id
      where fr.domain_id = 2
     )
select cte.*
from (select cte.*, 
             row_number() over (partition by domain_id, subdomain_name, type_name, content
                                order by changed_on desc
                               ) as seqnum
      from cte
     ) cte
where seqnum = 1;

请注意，我添加了表别名，因此查询更易于编写和阅读。

推荐阅读