问题求解。。

[yinsuxia]

如何查出表中数据里面按月分，每个月的最大日期的那一天的所有数据？
表结构 ：date originid usercount
就是要查出date列中每个月的最大日期，符合这个条件的记录。。。

[-晴天]

select * from tb a where not exists(select 1 from tb where convert(varchar(7),date,120)=convert(varchar(7),a.date,120) and date>a.date)

[快溜]

select * from tb a
 where [date]=(select max([date]) from tb
         where convert(varchar(7),[date],120)=convert(varchar(7),a.[date],120))

[-晴天]

or:

select * from tb a where not exists(select 1 from tb where year(date)=year(a.date) and month(date)=month(a.date) and date>a.date)

[DataBox-MDX]

select tb.* from
(
select date,row_number(partition by month(date) order by date desc)as RN
from tb
) as tba 
inner join tb on tba.RN=1 and tb.date=tba.date

[--小F--]

select * from tb t where [date]=(select max([date]) from tb where datediff(mm,[date],t.[date])=0)