怎么求出按一定时间间隔(比如秒)的最高和最低值?SQL Profiler跟踪的结果:结构大概这样:
TextData Starttime Duration
TextData是发生的SQL 语句我怎么能求出1秒钟发生的最多和最少的SQL语句的数量和耗时?
TextData Starttime Duration
TextData是发生的SQL 语句我怎么能求出1秒钟发生的最多和最少的SQL语句的数量和耗时?
调试欢乐多
我现在已经导入到表里了,SQL语句怎么写?
贴出前十行select 出来的结果。
SELECT OrderQueue.OrderNo As OrderCode ,t_im_form_mas.f_form_id As FormID ,t_im_form_mas.f_form_name As FormName ,OrderQueue.OrderCount ,OrderQueue.OrderTime ,t_distriwk.DeliveryDate As DeliveryDate ,OrderQueue.ReceiveTime ,t_distriwk.DistriDate As DistriDate ,t_distriwk.OridReceiveDate As OridReceiveDate ,IsNull(t_distriwk.DueDate,OrderQueue.DueDate) As DueDate ,OrderQueue.PriorityID ,(SELECT TOP 1 SrcPath FROM t_scanningfile_tbl WITH(nolock) WHERE f_store_customer_id = f_distriwk_customer_id AND f_store_receipt_number = f_distriwk_receipt_number) As SrcPath FROM t_distriwk WITH(nolock) LEFT OUTER JOIN t_im_form_mas WITH(nolock) ON t_distriwk.f_distriwk_customer_id = t_im_form_mas.f_form_customer_id and t_distriwk.FormID = t_im_form_mas.f_form_id RIGHT OUTER JOIN OrderQueue WITH(nolock) ON t_distriwk.f_distriwk_receipt_number = OrderQueue.OrderNo AND t_distriwk.f_distriwk_customer_id = OrderQueue.CustomerID WHERE OrderQueue.OrderTime >= CONVERT(DATETIME, '4/1/2009 12:00:00 AM', 102) AND t_distriwk.OridReceiveDate Is Null ORDER BY OrderQueue.OrderTime NULL NULL NULL NULL NULL NULL NULL 740 .Net SqlClient Data Provider sa 62 0 2009-04-01 11:15:52.673 NULL 115 0 0 NULL NULL NULL NULL NULL NULL NULL 12 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
SELECT OrderQueue.OrderNo As OrderCode ,t_im_form_mas.f_form_id As FormID ,t_im_form_mas.f_form_name As FormName ,OrderQueue.OrderCount ,OrderQueue.OrderTime ,t_distriwk.DeliveryDate As DeliveryDate ,OrderQueue.ReceiveTime ,t_distriwk.DistriDate As DistriDate ,t_distriwk.OridReceiveDate As OridReceiveDate ,IsNull(t_distriwk.DueDate,OrderQueue.DueDate) As DueDate ,OrderQueue.PriorityID ,(SELECT TOP 1 SrcPath FROM t_scanningfile_tbl WITH(nolock) WHERE f_store_customer_id = f_distriwk_customer_id AND f_store_receipt_number = f_distriwk_receipt_number) As SrcPath FROM t_distriwk WITH(nolock) LEFT OUTER JOIN t_im_form_mas WITH(nolock) ON t_distriwk.f_distriwk_customer_id = t_im_form_mas.f_form_customer_id and t_distriwk.FormID = t_im_form_mas.f_form_id RIGHT OUTER JOIN OrderQueue WITH(nolock) ON t_distriwk.f_distriwk_receipt_number = OrderQueue.OrderNo AND t_distriwk.f_distriwk_customer_id = OrderQueue.CustomerID WHERE OrderQueue.OrderTime >= CONVERT(DATETIME, '4/1/2009 12:00:00 AM', 102) AND t_distriwk.OridReceiveDate Is Null ORDER BY OrderQueue.OrderTime NULL NULL NULL NULL NULL NULL NULL 992 .Net SqlClient Data Provider sa 55 0 2009-04-01 11:15:54.220 NULL 115 0 0 NULL NULL NULL NULL NULL NULL NULL 12 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
(select convert(varchar(19),datetime,121) [datetime],count(1) as con from tb group by convert(varchar(19),datetime,121))
select * from a where [con] =(select max(con) from a) or [con]=(select min(con) from a)
DECLARE @T TABLE (SQL INT,时间 datetime,耗时 dec(9,2))
insert @t select 1,'2009-04-01 11:15:52.673',0.25
insert @t select 1,'2009-04-01 11:15:52.675',0.25
insert @t select 3,'2009-04-01 11:15:51.673',0.25
insert @t select 4,'2009-04-01 11:15:50.673',0.25
--我怎么能求出1秒钟发生的最多和最少的SQL语句的数量和耗时?select top 1 SQL,convert(varchar,时间,120) 时间,sum(耗时) 耗时 ,计数=count(1)
from @t
group by SQL,convert(varchar,时间,120)
order by 计数 desc/*
SQL 时间 耗时 计数
----------- ------------------------------ --------------------------------------- -----------
1 2009-04-01 11:15:52 0.50 2(1 行受影响)
*/大概的,你参考一下。