我通过一个SQL 语句找出如下内容
select * from(select min([id])as [id], min([UserName]) as [UserName],CONVERT(varchar(10) , Addtime, 121 ) as addTime,count(*) as [a] from [hdsource].[dbo].[LoginRecord] where 1=1 and year(addtime)=2008 and month(addTime)=6 group by CONVERT(varchar(10) , addtime, 121 ) ) b order by id desc搜出的结果如下 ID USERNAME ADDTIME A58 ldg 2008-06-16 4
57 ldg 2008-06-15 1
54 ldg 2008-06-14 3
50 ldg 2008-06-12 4
49 ldg 2008-06-11 1
48 ldg 2008-06-09 1
40 ldg 2008-06-05 8
36 ldg 2008-06-03 4
34 ldg 2008-06-02 2
30 ldg 2008-06-01 4
1 ldgc 2008-06-29 1我想再对结果进行统计下
最后返回是这样,也就是统计他们每个月的总数 ,要求在我的SQL上改
USERNAME ADDTIME Aldg 2008-06 32
ldgc 2008-06 1
select * from(select min([id])as [id], min([UserName]) as [UserName],CONVERT(varchar(10) , Addtime, 121 ) as addTime,count(*) as [a] from [hdsource].[dbo].[LoginRecord] where 1=1 and year(addtime)=2008 and month(addTime)=6 group by CONVERT(varchar(10) , addtime, 121 ) ) b order by id desc搜出的结果如下 ID USERNAME ADDTIME A58 ldg 2008-06-16 4
57 ldg 2008-06-15 1
54 ldg 2008-06-14 3
50 ldg 2008-06-12 4
49 ldg 2008-06-11 1
48 ldg 2008-06-09 1
40 ldg 2008-06-05 8
36 ldg 2008-06-03 4
34 ldg 2008-06-02 2
30 ldg 2008-06-01 4
1 ldgc 2008-06-29 1我想再对结果进行统计下
最后返回是这样,也就是统计他们每个月的总数 ,要求在我的SQL上改
USERNAME ADDTIME Aldg 2008-06 32
ldgc 2008-06 1
[UserName],
LEFT([addTime],7) as [addTime]),
count(*) as A
from
(
select * from(select min([id])as [id], min([UserName]) as [UserName],CONVERT(varchar(10) , Addtime, 121 ) as addTime,count(*) as [a] from [hdsource].[dbo].[LoginRecord] where 1=1 and year(addtime)=2008 and month(addTime)=6 group by CONVERT(varchar(10) , addtime, 121 ) ) b
) c
group by [UserName], LEFT([addTime],7)
把日期类型的年和月取出来,group by USERNAME,年,月 不就行了?
from [hdsource].[dbo].[LoginRecord]
where year(addtime)=2008 and month(addTime)=6
group by UserName, CONVERT(varchar(7), Addtime, 121)
(
ID int, USERNAME varchar(20),ADDTIME datetime, A int
)insert into userInfos
select '58', 'ldg', '2008-06-16', 4 union all
select '57', 'ldg', '2008-06-15', 1 union all
select '54', 'ldg', '2008-06-14', 3 union all
select '50', 'ldg', '2008-06-11', 4 union all
select '49', 'ldg', '2008-06-09', 1 union all
select '40', 'ldg', '2008-06-09', 8 union all
select '36', 'ldg', '2008-06-03', 4 union all
select '34', 'ldg', '2008-06-02', 2 union all
select '30', 'ldg', '2008-06-01', 8 union all
select '1', 'dgc', '2008-06-29', 1
select USERNAME,substring(CONVERT(varchar(10) , Addtime, 121 ),0,8) as [Datetime],count(a) from userInfos group by USERNAME,substring(CONVERT(varchar(10) , Addtime, 121 ),0,8)ldg 2008-06 32
ldgc 2008-06 1
select USERNAME,left(CONVERT(varchar(10) , Addtime, 121 ),7) as [Datetime],sum(a)
from userInfos
group by USERNAME,left(CONVERT(varchar(10) , Addtime, 121 ),7)
//结果
dgc 2008-06 1
ldg 2008-06 35