sql - 按不在 SQL select 语句中的字段排序

Question

我需要创建一个仅提取 customer_no 列的查询(因为软件限制如此，我无法在外部对其进行编码)。但是我需要能够通过 create_dt (反向)列对数据进行排序。代码/SQL 限制了我使用以下内容，因为为了按某些内容进行排序，数据必须出现在 select 语句中。

我不能让它出现在那里——有什么办法可以解决这个问题吗？

 Select Distinct top 3500 a.customer_no 
  From T_CUSTOMER a  WITH (NOLOCK)
  JOIN (Select a1.customer_no From VXS_CUST_TKW a1 WITH (NOLOCK) Where a1.tkw in (141)) as e ON      e.customer_no = a.customer_no
  Where 1 = 1
 order by a.create_dt desc

Answer 1

当然可以。您的查询看起来像 SQL Server，这可能会执行您想要的操作:

  Select top 3500 a.customer_no 
  From T_CUSTOMER a  WITH (NOLOCK) JOIN
       (Select a1.customer_no
        From VXS_CUST_TKW a1 WITH (NOLOCK)
        Where a1.tkw in (141)
       ) e
       ON e.customer_no = a.customer_no
  Where 1 = 1
  group by a.customer_no
  order by max(a.create_dt) desc;

MySQL 中的等效查询如下所示:

  Select a.customer_no 
  From T_CUSTOMER a JOIN
       (Select a1.customer_no
        From VXS_CUST_TKW a1 
        Where a1.tkw in (141)
       ) e
       ON e.customer_no = a.customer_no
  Where 1 = 1
  order by a.create_dt desc
  limit 3500;

我删除了 distinct因为它可能没有必要。如果是，请重新添加。