SQL查询,如果列值存在,则按其分组,否则列出所有记录

注意:我已经在使用UNION来解决这个问题,但正在寻找更好的解决方案。

UNION 是 最佳解决方案。

with
  null_report as
  (
    select report_id, report_created_by, versions, report_number
    from reports
    where report_number is null
  ),
  first_non_null_report as
  (
    select top(1) with ties
      report_id, report_created_by, versions, report_number
    from reports report_number
    where report_number is not null
    order by row_number() over (partition by report_number order by versions)
  )
select * from null_report
union all
select * from first_non_null_report
order by report_id;

演示: https://dbfiddle.uk/Y5qLaJA5

排序后可以获得行号 versions 每 report_created_by 使用 row_number() over () ,然后选择第一条记录:

with lowest_report_versions as (
  select 
    reports.*, 
    row_number() over (partition by report_created_by order by versions) as r
   from reports
)
select * 
from lowest_report_versions 
where r = 1 
order by report_id

db<>fiddle