假如有表如下:
字段1 字段2
a 2010-05-13 08:59:51.73
b 2010-05-13 08:62:51.73
c 2010-05-13 08:60:51.73现在我想建一个视图,要求如下:
选按字段2(时间列)进行从小到大排序。然后计算字段2(时间列),计算方式是,第二行减第一行,将结果写在第一行后面一列中,最后一个行因为没有计算字段了, 所以留空。最终结果为:
字段1 字段2 字段(秒)3
a 2010-05-13 08:59:51.73 1
c 2010-05-13 08:60:51.73 2
b 2010-05-13 08:62:51.73
请问这个视图怎么写,谢谢。
字段1 字段2
a 2010-05-13 08:59:51.73
b 2010-05-13 08:62:51.73
c 2010-05-13 08:60:51.73现在我想建一个视图,要求如下:
选按字段2(时间列)进行从小到大排序。然后计算字段2(时间列),计算方式是,第二行减第一行,将结果写在第一行后面一列中,最后一个行因为没有计算字段了, 所以留空。最终结果为:
字段1 字段2 字段(秒)3
a 2010-05-13 08:59:51.73 1
c 2010-05-13 08:60:51.73 2
b 2010-05-13 08:62:51.73
请问这个视图怎么写,谢谢。
(select m.*,rn=count(n.*) from tb m left join tb n on m.字段2>=n.字段2 group by m.字段1,m.字段2) t
join (select m.*,rn=count(n.*) from tb m left join tb n on m.字段2>=n.字段2 group by m.字段1,m.字段2) p on t.rn=p.rn - 1
insert @t select 'a', '2010-05-13 08:49:51.73'
insert @t select 'b', '2010-05-13 08:52:51.73'
insert @t select 'c', '2010-05-13 08:55:51.73'
select *,
datediff(mi,col2,(select top 1 col2
from @t
where col1>t.col1 ))
from @t t
/*
col1 col2
---------- ----------------------- -----------
a 2010-05-13 08:49:51.730 3
b 2010-05-13 08:52:51.730 3
c 2010-05-13 08:55:51.730 NULL(3 個資料列受到影響)
*/
毫秒也需要加入计算,这个是SMT设备装订IC的时间,必须要毫秒。
select t.字段1 ,t.字段2,datediff(mi,t.字段2,p.字段2) [字段(秒)3] from
(select m.*,rn=count(n.字段1) from tb m left join tb n on m.字段2>=n.字段2 group by m.字段1,m.字段2) t
left join (select m.*,rn=count(n.字段1) from tb m left join tb n on m.字段2>=n.字段2 group by m.字段1,m.字段2) p on t.rn=p.rn - 1/*
字段1 字段2 字段(秒)3
---------- ------------------------------------------------------ -----------
a 2010-05-13 08:49:51.730 1
c 2010-05-13 08:50:51.730 2
b 2010-05-13 08:52:51.730 NULL(所影响的行数为 3 行)*/
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
字段1 char(2),
字段2 datetime
)
go
--插入测试数据
insert into tb select 'a','2010-05-13 08:53:51.73'
union all select 'b','2010-05-13 08:57:51.73'
union all select 'c','2010-05-13 08:59:51.73'
go
--代码实现
if object_id('test.dbo.view_test') is not null drop view view_test
go
create view view_test
as
select a.字段1,a.字段2,字段3=isnull(cast(datediff(n,a.字段2,b.字段2)as char),'') from
(select idd=row_number()over(order by getdate()),* from tb)a
left join (select idd=row_number()over(order by getdate()),* from tb)b
on a.idd=b.idd-1
go
select * from view_test/*测试结果字段1 字段2 字段3
--------------------------------
a 2010-05-13 08:53:51.730 4
b 2010-05-13 08:57:51.730 2
c 2010-05-13 08:59:51.730 (3 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-05-14 15:08:58
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([a] [nvarchar](10),[b] [datetime])
INSERT INTO [tb]
SELECT 'a','2010-05-13 08:49:51.73' UNION ALL
SELECT 'b','2010-05-13 08:52:51.73' UNION ALL
SELECT 'c','2010-05-13 08:55:51.73'--SELECT * FROM [tb]-->SQL查询如下:
;with t as
(
select rn=row_number()over(ORDER by getdate()),*
from tb
)
select a.a,a.b,datediff(ms,a.b,b.b) bx
from t a
left join t b
on a.rn=b.rn-1
/*
a b bx
---------- ----------------------- -----------
a 2010-05-13 08:49:51.730 180000
b 2010-05-13 08:52:51.730 180000
c 2010-05-13 08:55:51.730 NULL(3 行受影响)
*/
go
if object_id('test.dbo.tb') is not null drop table tb
create table tb
(
col1 char(2),
col2 datetime
)
go
--插入测试数据
insert into tb select 'a','2010-05-13 08:56:51.73'
union all select 'b','2010-05-13 08:58:51.73'
union all select 'c','2010-05-13 08:59:51.73'
select t1.col1,t1.col2,abs(datediff(mi,t2.col2,t1.col2)) '差' from
(
select *,
(select count(*)+1 from tb where col2<t.col2) row
from tb t
) t1
join
(
select *,
(select count(*)+1 from tb where col2<t.col2) row
from tb t
) t2
on t1.row=t2.row-1col1 col2 差
---- ----------------------- -----------
a 2010-05-13 08:56:51.730 2
b 2010-05-13 08:58:51.730 1(2 行受影响)
go
if object_id('test.dbo.tb') is not null drop table tb
create table tb
(
col1 char(2),
col2 datetime
)
go
--插入测试数据
insert into tb select 'a','2010-05-13 08:56:51.73'
union all select 'b','2010-05-13 08:58:51.73'
union all select 'c','2010-05-13 08:59:51.73'
select t1.col1,t1.col2,abs(datediff(ms,t2.col2,t1.col2)) '差' from
(
select *,
(select count(*)+1 from tb where col2<t.col2) row
from tb t
) t1
left join
(
select *,
(select count(*)+1 from tb where col2<t.col2) row
from tb t
) t2
on t1.row=t2.row-1col1 col2 差
---- ----------------------- -----------
a 2010-05-13 08:56:51.730 120000
b 2010-05-13 08:58:51.730 60000
c 2010-05-13 08:59:51.730 NULL(3 行受影响)
(
字段1 char(2),
字段2 datetime
)
go
insert into tb select 'a','2010-05-13 08:53:51.73'
union all select 'b','2010-05-13 08:57:51.73'
union all select 'c','2010-05-13 08:59:51.73'
select a.字段1,a.字段2,字段3=isnull(cast(datediff(n,a.字段2,b.字段2)as char),'') from
(select idd=row_number()over(order by 字段2),* from tb)a
left join (select idd=row_number()over(order by 字段2),* from tb)b
on a.idd=b.idd-1