select convert(char(10),time,120) as time,sid,feecode
from tablename a
where time=(select min(time) from tablename
where sid=a.sid
and convert(char(7),time,120)=convert(char(7),a.time,120)
)
from tablename a
where time=(select min(time) from tablename
where sid=a.sid
and convert(char(7),time,120)=convert(char(7),a.time,120)
)
from tablename a
where time=(select min(time) from tablename
where sid=a.sid and simno=a.simno -这里不知道该不该加,你自己考虑
and convert(char(7),time,120)=convert(char(7),a.time,120)
)
select [time]=convert(char(10),a.[time],120),a.sid,feecode=sum(a.feecode)
from 表 a join(
select simno,[time]=min([time])
from 表
group by convert(char(7),[time],120),simno
)b on a.[time]=b.[time] and a.simno=b.simno
group by convert(char(10),a.[time],120),a.sid
order by time,sid
create table 表([time] datetime,simno varchar(5),Sid varchar(4),feecode int)
insert 表 select '2004-04-04 09:23:33.000','12345',1000,1
union all select '2004-04-05 09:23:33.000','12345',1000,1
union all select '2004-04-05 09:23:33.000','10000',1001,2
union all select '2004-04-06 09:23:33.000','12345',1000,1
union all select '2004-05-02 09:23:33.000','12345',1000,1
go--查询
select [time]=convert(char(10),a.[time],120),a.sid,feecode=sum(a.feecode)
from 表 a join(
select simno,[time]=min([time])
from 表
group by convert(char(7),[time],120),simno
)b on a.[time]=b.[time] and a.simno=b.simno
group by convert(char(10),a.[time],120),a.sid
order by time,sid
go--删除测试
drop table 表/*--测试结果
time sid feecode
---------- ---- -----------
2004-04-04 1000 1
2004-04-05 1001 2
2004-05-02 1000 1(所影响的行数为 3 行)
--*/