一个问题,难倒无数英雄啊...
实现日统计:选择日期,UserID,InterfaceID,动态实现统计选择时期内每个小时的上行总数、状态同步统计、成功率统计。如:
用户<select><item>UserID</item></select>
通道<select><item>InterfaceID</item></select>
日期:自己用插件或脚本实现均可(如从2010-5-1 到 2010-5-1,即2010-5-1当天)
表格:
同步数 10 25 。。。。
上行数 10 28 。。。。
成功率 100% 89.3% 。。。。
时间 0点 1点 2点 。。。
数据库字段
表和主要字段:
T_User 用户表:ID,UserName,password...
T_Mo 上行表:InterfaceID(通道) UserID RegDate(日期)
T_Report 状态同步表(status=1表示成功):UserID,InterfaceID,Status,RegDate
给点思路啊 谢了!
[解决办法]
CREATE PROCEDURE [dbo].[Total_pag] @UserID int,--用户ID @InterfaceID int,--通道ID @Start_Times datetime, @End_Times datetime, @style intAS declare @times1 intdeclare @times2 intdeclare @Up_sum intdeclare @Stau_sum intif @style =1begin select count(DATEPART(HH,RegDate)) as Up_sum,DATEPART(HH,RegDate) as TR into #T_Mo from dbo.T_Mo where RegDate between @Start_Times and @End_Times and UserID=@UserID and InterfaceID=@InterfaceID GROUP BY DATEPART(HH,RegDate) order by DATEPART(HH,RegDate) desc select count(DATEPART(HH,RegDate)) as Stau_sum,DATEPART(HH,RegDate) as RR into #T_Report from dbo.T_Report where RegDate between @Start_Times and @End_Times and UserID=@UserID and InterfaceID=@InterfaceID and Status=1 GROUP BY DATEPART(HH,RegDate) order by DATEPART(HH,RegDate) desc select *,LTRIM(CONVERT(DEC(18,2),Stau_sum*100.0/Up_sum))+'%' as sty into ##Total from #T_Mo inner join #T_Report on #T_Mo.TR=#T_Report.RRend if @style =2begin select count(DATEPART(dd,RegDate)) as Up_sum, DATEPART(dd,RegDate) as Mre_Date into #T_Mo from dbo.T_Mo where RegDate between @Start_Times and @End_Times and UserID=@UserID and InterfaceID=@InterfaceID GROUP BY DATEPART(dd,RegDate) order by DATEPART(dd,RegDate) desc select count(DATEPART(dd,RegDate)) as Stau_sum,DATEPART(dd,RegDate) as Rre_Date into #T_Report from dbo.T_Report where RegDate between @Start_Times and @End_Times and UserID=@UserID and InterfaceID=@InterfaceID and Status=1 GROUP BY DATEPART(dd,RegDate) order by DATEPART(dd,RegDate) desc select *,LTRIM(CONVERT(DEC(18,2),Stau_sum*100.0/Up_sum))+'%' as sty into ##Total from #T_Mo inner join #T_Report on #T_Mo.Mre_Date=#T_Report.Rre_Date endif @style =3begin select count(UserID) as Up_sum,UserID into #T_Mo from T_Mo where InterfaceID=@InterfaceID and RegDate between @Start_Times and @End_Times group by UserID order by UserID select count(UserID) as Stau_sum,UserID as U_ID into #T_Report from T_Report where InterfaceID=@InterfaceID and RegDate between @Start_Times and @End_Times and Status=1 group by UserID order by UserID select *,LTRIM(CONVERT(DEC(18,2),Stau_sum*100.0/Up_sum))+'%' as sty into ##Total from #T_Mo inner join #T_Report on #T_Mo.UserID=#T_Report.U_ID enddrop table #T_Modrop table #T_Reportdrop table ##Total
[解决办法]
我想我大概明白了一点,你的意思想从T_Mo 上行表和T_Report 状态同步表中的数据得到你想要的结果,不知道有木有理解错误
[解决办法]
数据库字段
表和主要字段:
T_User 用户表:ID,UserName,password...
T_Mo 上行表:InterfaceID(通道) UserID RegDate(日期)
T_Report 状态同步表(status=1表示成功):UserID,InterfaceID,Status,RegDate
给出上面表的实际的一些数据,再给出想要的数据,这样别人容易明白
[解决办法]
汗。还是自己想想怎么才能够把这个问题描述的清楚。这样提问可能事半功倍噢。否则再怎么问,别人要猜你的意图。太难了。
[解决办法]
select a.reportcount,a.interfaceid,a.reporthout,b.mocount from (select count(id) reportcount ,interfaceid,datepart(hh,regdate) reporthour from t_report where convert(varchar(10),regdate,111)='2010/05/01' and status=1 group by datepart(hh,regdate),interfaceid) a left join (select count(id) mocount ,interfaceid,datepart(hh,regdate) mohour from t_mo where convert(varchar(10),regdate,111)='2010/05/01' group by datepart(hh,regdate),interfaceid) b on b.interfaceid=a.interfaceid and a.reporthour=b.mohour