在使用左联接时选择distinct id，然后在两个表上按顺序排序

Question

我有两个表，我将根据两个表上复杂的order子句获得与第一个不同的id。我很困惑如何通过使用区分的或groub，因为这两种方法都阻止在其他列上使用order by。

我的代码使用了distinct：

select distinct(shops.id) from shops 
left join expiration on shops.id=expiration.shopid 
order by 
  shops.grade desc,
  expiration.startdate asc,
  expiration.enddate desc,
  shops.id

如果指定了select，则按项排序必须出现在选择列表中。

我的代码使用组：

select max(shops.id) as shopid from shops 
left join expiration on shops.id=expiration.shopid
group by shopid
order by 
  shops.grade desc,
  expiration.startdate asc,
  expiration.enddate desc,
  shops.id

列"shops.grade“在ORDER子句中无效，因为它既不包含在聚合函数中，也不包含在GROUP子句中。

Answer 1

可以在ORDER BY中使用聚合函数

SELECT shops.id AS shopid -- No need to use MAX(...) here
FROM shops 
LEFT JOIN expiration ON shops.id=expiration.shopid
GROUP BY shopid
ORDER BY
  MAX(shops.grade) DESC,
  MIN(expiration.startdate) ASC,
  MAX(expiration.enddate) DESC,
  shops.id

Answer 2

如果我正确理解，您将有一个优先级查询。如果是这样，您可以使用row_number()而不是select distinct或group by来处理它。

select se.shopid
from (select s.id as shopid,
             row_number() over (partition by s.id
                                order by s.grade desc, e.startdate asc, e.enddate desc, s.id
                               ) as seqnum
      from shops s left join
           expiration e
           on s.id = e.shopid
     ) se
where seqnum = 1;