有一个表 A ,表格式如下: number date
8 2009/1/11 2:00
7 2009/1/11 5:00
6 2009/1/11 12:00
5 2009/1/11 18:00
4 2009/1/12 4:00
3 2009/1/12 10:00
2 2009/1/12 12:00
1 2009/1/11 17:00想得到当天的最早时间与最晚时间的number的差值, 即如下的结果:
差
3
3
这个语句该怎么写,请各位帮帮忙!谢谢了!
8 2009/1/11 2:00
7 2009/1/11 5:00
6 2009/1/11 12:00
5 2009/1/11 18:00
4 2009/1/12 4:00
3 2009/1/12 10:00
2 2009/1/12 12:00
1 2009/1/11 17:00想得到当天的最早时间与最晚时间的number的差值, 即如下的结果:
差
3
3
这个语句该怎么写,请各位帮帮忙!谢谢了!
convert(varchar(8),date,112),
datediff(hh,min(date),max(date))
from
A
group by
convert(varchar(8),date,112)
如:2009/1/11这天,最早时间是2009/1/11 2:00 它对应的number是8,最晚时间是2009/1/11 18:00它对应的number是5,这两个number的差值是3
convert(varchar(8),date,112),
max(number)-min(number)
from
A
group by
convert(varchar(8),date,112)
SELECT DATEDIFF ( mi , (Select min(date ) from table1) , (Select max(date ) from table1))
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-09 14:14:36
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([number] int,[date] datetime)
insert [tb]
select 8,'2009/1/11 2:00' union all
select 7,'2009/1/11 5:00' union all
select 6,'2009/1/11 12:00' union all
select 5,'2009/1/11 18:00' union all
select 4,'2009/1/12 4:00' union all
select 3,'2009/1/12 10:00' union all
select 2,'2009/1/12 12:00' union all
select 1,'2009/1/12 17:00'
--------------开始查询--------------------------
;with f1 as
(
select
*
from
[tb] t
where
date=(select min(date) from tb where convert(varchar(10),date,120)=convert(varchar(10),t.date,120))
),
f2 as
(
select
*
from
[tb] t
where
date=(select max(date) from tb where convert(varchar(10),date,120)=convert(varchar(10),t.date,120))
)
select f1.number-f2.number from f1,f2 where convert(varchar(10),f1.date,120)=convert(varchar(10),f2.date,120)
----------------结果----------------------------
/*
-----------
3
3(2 行受影响)
*/
insert into tb
select 8, '2009/1/11 2:00'
union all
select 7, '2009/1/11 5:00'
union all
select 6, '2009/1/11 12:00'
union all
select 5, '2009/1/11 18:00'
union all
select 4, '2009/1/12 4:00'
union all
select 3, '2009/1/12 10:00'
union all
select 2, '2009/1/12 12:00'
union all
select 1, '2009/1/11 17:00'
select sum(number) 差 ,dd from
(select number,convert(char(10),date,120) dd from tb t1 where not exists (select 1 from tb where convert(char(10),date,120)=convert(char(10),t1.date,120) and convert(char(5),date,114)<convert(char(5),t1.date,114))
union all
select -number,convert(char(10),date,120) from tb t1 where not exists (select 1 from tb where convert(char(10),date,120)=convert(char(10),t1.date,120) and convert(char(5),date,114)>convert(char(5),t1.date,114))
) tmp
group by dd/*
-----------------------
3 2009-01-11
2 2009-01-12
*/
drop table tb
convert(varchar(10),date,120),
max([number])-min([number])AS 差值
from
A
group by
convert(varchar(10),date,120)
INSERT @TB
SELECT 8, '2009/1/11 2:00' UNION ALL
SELECT 7, '2009/1/11 5:00' UNION ALL
SELECT 6, '2009/1/11 12:00' UNION ALL
SELECT 5, '2009/1/11 18:00' UNION ALL
SELECT 4, '2009/1/12 4:00' UNION ALL
SELECT 3, '2009/1/12 10:00' UNION ALL
SELECT 2, '2009/1/12 12:00' UNION ALL
SELECT 1, '2009/1/11 17:00'
SELECT SUM(CASE WHEN T.[date]=MAXD THEN -number ELSE number END) AS DIF
FROM @TB AS T JOIN (
SELECT MAX([date]) AS MAXD,MIN([date]) AS MIND
FROM @TB
GROUP BY CONVERT(VARCHAR(10),[date],120)
) AS T2
ON T.[date]=MAXD OR T.[date]=MIND
GROUP BY CONVERT(VARCHAR(10),[date],120)
/*
DIF
-----------
3
2
*/
--测试数据
DECLARE @TB TABLE([number] INT, [date] DATETIME)
INSERT @TB
SELECT 8, '2009/1/11 2:00' UNION ALL
SELECT 7, '2009/1/11 5:00' UNION ALL
SELECT 6, '2009/1/11 12:00' UNION ALL
SELECT 5, '2009/1/11 18:00' UNION ALL
SELECT 4, '2009/1/12 4:00' UNION ALL
SELECT 3, '2009/1/12 10:00' UNION ALL
SELECT 2, '2009/1/12 12:00' UNION ALL
SELECT 1, '2009/1/12 17:00'
--查询
select a.number - b.number as [差]
from
(select * from @tb t where date =
(select min(date) from @tb where convert(varchar(10),date,120) = convert(varchar(10),t.date,120))) a,
(select * from @tb t where date =
(select max(date) from @tb where convert(varchar(10),date,120) = convert(varchar(10),t.date,120))) b
where convert(varchar(10),a.date,120) = convert(varchar(10),b.date,120)
--结果
---------------------------
差
3
3
(
number int identity(1,1) primary key,
date datetime
)
insert into #date select '2009/1/11 17:00'
insert into #date select '2009/1/12 12:00'
insert into #date select '2009/1/12 10:00'
insert into #date select '2009/1/12 4:00'
insert into #date select '2009/1/11 18:00'
insert into #date select '2009/1/11 12:00'
insert into #date select '2009/1/11 5:00'
insert into #date select '2009/1/11 2:00'select (d2.number-d1.number) number
from
(
select number,date from #date where date in
(select max(date) from #date group by convert(varchar(10),date,120) )
) d1
,
(
select number,date from #date where date in
(select min(date) from #date group by convert(varchar(10),date,120) )
) d2
where convert(varchar(10),d1.date,120)=convert(varchar(10),d2.date,120)number
-----------
2
3