在TSQL中对重复行进行分组

Question

我在SQL Server 2005中有以下表格和数据：

create table LogEntries (
  ID int identity,
  LogEntry varchar(100)
)

insert into LogEntries values ('beans')
insert into LogEntries values ('beans')
insert into LogEntries values ('beans')
insert into LogEntries values ('cabbage')
insert into LogEntries values ('cabbage')
insert into LogEntries values ('beans')
insert into LogEntries values ('beans')

我想对重复的LogEntries进行分组，这样我就可以得到以下结果：

LogEntry  EntryCount
beans     3
cabbage   2
beans     2

除了使用游标之外，您还能想到在TSQL中执行此操作的任何方法吗？

Answer 1

这是该问题的基于集合的解决方案。性能可能会很差，但它是有效的:)

CREATE TABLE #LogEntries (
  ID INT IDENTITY,
  LogEntry VARCHAR(100)
)

INSERT INTO #LogEntries VALUES ('beans')
INSERT INTO #LogEntries VALUES ('beans')
INSERT INTO #LogEntries VALUES ('beans')
INSERT INTO #LogEntries VALUES ('cabbage')
INSERT INTO #LogEntries VALUES ('cabbage')
INSERT INTO #LogEntries VALUES ('carrots')
INSERT INTO #LogEntries VALUES ('beans')
INSERT INTO #LogEntries VALUES ('beans')
INSERT INTO #LogEntries VALUES ('carrots')

SELECT logentry, COUNT(*) FROM (
    SELECT logentry, 
    ISNULL((SELECT MAX(id) FROM #logentries l2 WHERE l1.logentry<>l2.logentry AND l2.id < l1.id), 0) AS id
    FROM #LogEntries l1
) AS a
GROUP BY logentry, id


DROP TABLE #logentries 

结果：

beans   3
cabbage 2
carrots 1
beans   2
carrots 1

第一组bean需要ISNULL()。

Answer 2

我想这样就行了..。虽然没有检查得太彻底

select 
    COUNT(*),subq.LogEntry 
from 
(
    select 
        ROW_NUMBER() OVER(ORDER BY id)-ROW_NUMBER() OVER(PARTITION BY logentry ORDER BY id) as t,*
    from 
        LogEntries
) subq 
group by 
    subq.t,subq.LogEntry 
order by 
    MIN(subq.ID)

Answer 3

SQL不是我的强项，但不会

SELECT LogEntry, COUNT(1) AS Counter FROM LogEntries GROUP BY LogEntry

真的吗？