SELECT *,count(DISTINCT(发贴时间的前8位)) AS times FROM table GROUP BY id
SELECT 作者id, 发贴时间 FROM table a WHERE (NOT EXISTS (SELECT 作者id FROM table b WHERE b.作者id = a.作者id AND b.发贴时间 > a.发贴时间)) ORDER BY 作者id, 发贴时间 DESC
declare @t table([作者ID] int ,[发贴时间] datetime) insert into @t select 11,'08-01-01 15:00 ' union select 12,'08-01-01 16:00 ' union select 13,'08-01-01 17:00 ' union select 12,'08-01-01 18:00 ' union select 12,'08-01-02 16:00 ' select id,count(*)as [发贴次数] from ( select [作者ID] as id from @t group by [作者ID], CONVERT (varchar(10),[发贴时间],120)) T group by T.id
select count(ID) as cnt,ID from table where str_date="xxxx-xx-xx" group by ID order by cnt
SELECT 作者id, 发贴时间 FROM table a WHERE (NOT EXISTS (SELECT 作者id FROM table b WHERE b.作者id = a.作者id AND b.发贴时间 > a.发贴时间)) ORDER BY 作者id, 发贴时间 DESC
FROM table a
WHERE (NOT EXISTS
(SELECT 作者id
FROM table b
WHERE b.作者id = a.作者id AND b.发贴时间 > a.发贴时间))
ORDER BY 作者id, 发贴时间 DESC
insert into @t
select 11,'08-01-01 15:00 ' union
select 12,'08-01-01 16:00 ' union
select 13,'08-01-01 17:00 ' union
select 12,'08-01-01 18:00 ' union
select 12,'08-01-02 16:00 '
select id,count(*)as [发贴次数] from (
select [作者ID] as id from @t group by [作者ID],
CONVERT (varchar(10),[发贴时间],120)) T group by T.id
FROM table a
WHERE (NOT EXISTS
(SELECT 作者id
FROM table b
WHERE b.作者id = a.作者id AND b.发贴时间 > a.发贴时间))
ORDER BY 作者id, 发贴时间 DESC