sql server-MS sql问题:最大值和GUID

Question

select  first(orderid), accountid 
from [Order] 
group by AccountId 
order by DateCreated desc

first((是无效函数。

max((不适用于唯一标识符

如何获取为所有帐户创建的最后一个订单ID？谢谢

Answer 1

类似(未经测试(的东西：

;WITH CTE_LatestOrders AS (
    select accountid, lastcreated = max(datecreated)
    from [Order]
    group by accountid
)
select
    accountid, orderid
from
    [Orders] o
    join CTE_LatestOrders l 
        on o.AccountID = l.AccountID 
        and o.datecreated = l.lastcreated

Answer 2

Max((适用于自MS SQL 2012 起的唯一标识符

Answer 3

您也可以继续下面的操作。

Select Temp.orderid, T.AccountId, T.DateCreated
From
(
    Select AccountId, max(DateCreated) as  DateCreated
    From [Order]
    Group By AccountId
)T
Inner Join [Order] Temp on Temp.AccountId = T.AccountId 
AND Temp.DateCreated = T.DateCreated

CTE不是UDT/temp表；将CTE视为仅为当前查询定义的视图。就像视图一样，CTE被展开并折叠到整个查询计划中。全局优化仍然会发生，但不要因为使用了CTE就只执行一次查询。这里有一个适合这个空间的琐碎例子：WITH vw AS(SELECT COUNT(*(c FROM Person(SELECT a.c，b.c FROM vw a，vw b；查询计划将清楚地显示两次扫描/聚合和一个联接，而不是仅仅投影两次相同的结果。