如何在Select语句中使用列，该语句中不在汇总函数中或在Group中按子句中的列？

Question

上面是桌子，在上面我必须在过去的采访中回答以下问题。

q。每个客户的最新订单价值？

我在面试中给出的答案：

select customerID, ordervalue, max(orderdate)
from office
group by customerID;

我知道，因为我们没有使用订单值总计或整个组中的订单值，因此此查询会在SQL中丢弃错误，但 < <强>我想知道如何回答这个问题。

在我过去的面试官中多次问一个我需要在何处使用列在何处，在某些语句中使用列，该列是不在汇总函数的，或者不在组中的。 所以我一般想知道什么是解决方法，以便我可以解决这些类型的问题或如何回答这些问题。

Answer 1

周围的工作取决于所要求的内容。对于您上述要求，我认为创建（customerId，max（orderdate））对是有意义的。

SELECT customerid, MAX(orderdate)
FROM office 
GROUP BY customerid;

然后，您可以使用它们匹配表格中所需的行。

SELECT customerid, ordervalue, orderdate
FROM office
WHERE (customerid, orderdate) IN 
    (SELECT customerid, MAX(orderdate) 
     FROM office 
     GROUP BY customerid);

请注意，这假设每个客户每天只有一个订单。如果有多个，您会看到每个客户的最新订单。如果需要，您也可以在外部查询上添加组。

SELECT customerid, MAX(ordervalue), orderdate
FROM office AS tt
WHERE (customerid, orderdate) IN 
    (SELECT customerid, MAX(orderdate) 
     FROM office
     GROUP BY customerid)
GROUP BY customerid, orderdate;

如果您在中需要的非聚合列在功能上取决于 组中的列，则可以在select>选择。

我们可以通过添加名称列来扩展您的示例，其中不同客户的名称可能相同。如果您想要名称而不是订单值，只需匹配外部查询的customerId即可获取name。

SELECT customerid, 
  (SELECT name FROM office WHERE customerid=o.customerid LIMIT 1) AS name,
  MAX(orderdate)
FROM office AS o
GROUP BY customerid;

Answer 2

您正在处理以下任务：汇总所有行以每个客户获得一个结果行，显示最大订单日期及其订单值。问题的问题是：您需要一个汇总功能来获取最大订单日期的值。我所知道的唯一具有此功能的DBMS是Oracle，保持第一/last。

因此，从不同角度看任务。不要认为在聚合方面可以计算一个组的值并添加一个组的值，并在所有组的行上获得最小值或最大值，因为毕竟您只想选择单行。 （也就是说，每个客户选择顶部1行。）为了选择行，您将使用子句的。

史蒂夫（Steve）在他的回答中显示了一种选择：

select *
from office
where (customerid, orderdate) in 
( 
  select customerid, max(orderdate)
  from office
  group by customerid
);

这是一种很好，直率的方法。 （不过，某些DBMS在条款中都不具有的元素。）

为客户获得“最佳”行的另一种方法是选择那些不存在更好的行的行：

select *
from office
where not exists
( 
  select null
  from office better
  where better.customerid = office.customerid
  and better.orderdate > office.orderdate
);

和然后，可以选择使用窗口函数（又称分析功能）来获取这些行。一个示例是获得最大日期以及行的数据：

select customerid, ordervalue, orderdate
from
(
  select
    customerid, ordervalue, orderdate,
    max(orderdate) over (partition by customerid) as max_orderdate
  from office
)
where orderdate = max_orderdate;

以及row_number，等级和dense_rank有窗口函数要分配按您想要的顺序到行的数字。您将它们编号为最好的行获得数字1并选择它们。这里的最大优势是：您可以应用任何订单，处理关系，不仅获得前1名，而且获得顶级n行。

select customerid, ordervalue, orderdate
from
(
  select
    customerid, ordervalue, orderdate,
    row_number() over (partition by customerid order by orderdate desc) as rn
  from office
)
where rn = 1;