sql查询报告-选择最小日期

I want to create a report which displays the stats for data in a sql server table.The table columns I am interested in are searchTerm, dateadded. Now I want to get the count of records for each searchterm but also display the earliest and latest dateadded for that searchterm. for example:

我想要创建一个报表，该报表显示sql server表中的数据统计信息。我感兴趣的表列是searchTerm, dateadd。现在，我想获取每个searchterm的记录计数，但也要显示为该searchterm添加的最早和最新的数据。例如:

select searchterm, count(*) as recCount from SearchTable order by searchterm.

will just give me the counts so I just need the dates and distinct records only.

只会给我计数，所以我只需要日期和不同的记录。

2 个解决方案

#1

Try this:

试试这个:

SELECT
    searchterm,
    MIN(dateadded) AS minDateAdded, 
    MAX(dateadded) AS maxDateAdded, 
    count(*) as recCount
FROM SearchTable 
GROUP BY searchterm

They GROUP BY will ensure that each row is distinct.

他们将确保每一行都是不同的。

#2

You can use MIN and MAX functions, plus the GROUP BY clause

可以使用最小和最大值函数，加上GROUP BY子句

SELECT searchterm, MIN(dateadded) AS earliest, MAX(dateadded) AS latest, COUNT(*) as recCount
FROM SearchTable
GROUP BY searchterm
ORDER BY searchterm

#1