SQL查询在计数中包含最新日期

Question

我有一个示例，我正在计算记录的数量，并将它们显示为CustomerIDCount。我也想包括LatestDate，但我不确定我是否需要另一个查询，或者我可以将其添加到这个查询中？

SQLFiddle

设置

CREATE TABLE log
(
[CustomerID] [int] NULL, 
[LogDate] [datetime] NULL
);

INSERT INTO log ([CustomerID], [LogDate])
VALUES
(1, 2021-02-12),
(2, 2021-02-12),
(1, 2021-02-12),
(3, 2021-03-12),
(4, 2021-02-12)
;

我的尝试：

SELECT
CustomerID, COUNT(*) CustomerIDCount
FROM
(SELECT  CustomerID
FROM log) g
GROUP BY
CustomerID
ORDER BY 
CustomerIDCount

结果：

	客户ID	客户ID计数
2	1
3	1
4	1
1	2

您不需要使用子查询，只需将Max(LogDate(添加到查询中即可。

select CustomerID, count(*) CustomerIDCount, max(LogDate) as max_LogDate
from log
group by CustomerID
order by CustomerIDCount

从这些有限的信息中很难判断，我假设您在客户ID上有一个GroupBy，带有COUNT。您可以将MAX(LatestDate(添加到该查询中吗？

SELECT CustomerID
, Count(*) AS CustomerIDCount
, Max(LogDate) AS LastestDate
FROM   log
GROUP
BY CustomerID
;