数据表Telinfo有如下字段:
[No] [InOrOut] [RecordFlag] [Line] [Res1] [StartTime] [EndTime] [TelNum] [OrgName]
我想对表进行查询统计处理,表中OrgName相同的记录合并成一条记录,总数计入[Times]字段,Res1的和计入 [SucTimes],请问如何实现?
举例:
1 1 2 2 1 1999-9-9 1999-9-10 1391452+++4 长沙三一
2 1 2 1 0 1999-7-9 1999-9-8 1365556+++6 长沙三一
合并成
1 1 2 2 1 1999-9-9 1999-9-10 1391452+++4 长沙三一 2 1
[No] [InOrOut] [RecordFlag] [Line] [Res1] [StartTime] [EndTime] [TelNum] [OrgName]
我想对表进行查询统计处理,表中OrgName相同的记录合并成一条记录,总数计入[Times]字段,Res1的和计入 [SucTimes],请问如何实现?
举例:
1 1 2 2 1 1999-9-9 1999-9-10 1391452+++4 长沙三一
2 1 2 1 0 1999-7-9 1999-9-8 1365556+++6 长沙三一
合并成
1 1 2 2 1 1999-9-9 1999-9-10 1391452+++4 长沙三一 2 1
max([StartTime]), max([EndTime]), max([TelNum]), [OrgName],Times]=count(*),
[SucTimes]=sum([Res1])
from Telinfo
group by [OrgName]
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(No int, InOrOut int, RecordFlag int, Line int, Res1 int, StartTime datetime, EndTime datetime, TelNum varchar(11), OrgName varchar(8))
insert into #
select 1, 1, 2, 2, 1, '1999-9-9', '1999-9-10', '1391452+++4', '长沙三一' union all
select 2, 1, 2, 1, 0, '1999-7-9', '1999-9-8', '1365556+++6', '长沙三一';with cte as
(
select *,
Times=count(1)over(partition by OrgName),
SucTimes=sum(Res1)over(partition by OrgName)
from #
)
select * from cte t where [No] = (select top 1 [No] from cte order by StartTime desc)/*
No InOrOut RecordFlag Line Res1 StartTime EndTime TelNum OrgName Times SucTimes
----------- ----------- ----------- ----------- ----------- ----------------------- ----------------------- ----------- -------- ----------- -----------
1 1 2 2 1 1999-09-09 00:00:00.000 1999-09-10 00:00:00.000 1391452+++4 长沙三一 2 1
*/