在SQL中的select语句中按字段排序 [英] Ordering by a field not in the select statement in SQL

问题描述

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

I need to create a query that pulls only the customer_no column (because the software restrictions are as such, and I can't code it externally). But I need to be able to sort the data by create_dt (in reverse) column. The code/SQL is restricting me in using the following because in order to sort by something that data has to appear int the select statement.

我无法在其中显示–有什么办法解决吗?

I can't have it appear there – is there any way around this?

 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

推荐答案

当然可以.您的查询看起来像SQL Server，可以在其中执行您想要的操作:

Of course you can. Your query looks like SQL Server, where this will likely do what you want:

  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中的等效查询如下:

The equivalent query in MySQL would look like:

  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，因为可能没有必要.如果是这样，请将其重新添加.

I removed the distinct because it may not be necessary. If it is, add it back in.

这篇关于在SQL中的select语句中按字段排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！