drop table test
create table test(id int identity(1,1),Time_ datetime,A int)
truncate table testinsert into test values('2005-5-5 10:33:00','4')
insert into test values('2005-5-5 10:35:00','6')
insert into test values('2005-5-5 10:43:00','7')
insert into test values('2005-5-5 10:13:00','3')
insert into test values('2005-5-5 10:53:00','1')
insert into test values('2005-5-5 10:23:00','4')
insert into test values('2005-5-5 10:33:00','6')
insert into test values('2005-5-5 10:35:00','4')
insert into test values('2005-5-5 10:43:00','6')
insert into test values('2005-5-5 10:13:00','7')
insert into test values('2005-5-5 10:53:00','8')
insert into test values('2005-5-5 10:23:00','9')
按照left(convert(varchar(100),time_,21),15)进行group by 取A的Min值,
而且对于时间中缺少的比如2005-05-05 00:0的Min值取为○;drop table test刚才太急了
create table test(id int identity(1,1),Time_ datetime,A int)
truncate table testinsert into test values('2005-5-5 10:33:00','4')
insert into test values('2005-5-5 10:35:00','6')
insert into test values('2005-5-5 10:43:00','7')
insert into test values('2005-5-5 10:13:00','3')
insert into test values('2005-5-5 10:53:00','1')
insert into test values('2005-5-5 10:23:00','4')
insert into test values('2005-5-5 10:33:00','6')
insert into test values('2005-5-5 10:35:00','4')
insert into test values('2005-5-5 10:43:00','6')
insert into test values('2005-5-5 10:13:00','7')
insert into test values('2005-5-5 10:53:00','8')
insert into test values('2005-5-5 10:23:00','9')
按照left(convert(varchar(100),time_,21),15)进行group by 取A的Min值,
而且对于时间中缺少的比如2005-05-05 00:0的Min值取为○;drop table test刚才太急了
insert into test values('2005-5-5 10:33:00','4')
insert into test values('2005-5-5 10:35:00','6')
insert into test values('2005-5-5 10:43:00','7')
insert into test values('2005-5-5 10:13:00','3')
insert into test values('2005-5-5 10:53:00','1')
insert into test values('2005-5-5 10:23:00','4')
insert into test values('2005-5-5 10:33:00','6')
insert into test values('2005-5-5 10:35:00','4')
insert into test values('2005-5-5 10:43:00','6')
insert into test values('2005-5-5 10:13:00','7')
insert into test values('2005-5-5 10:53:00','8')
insert into test values('2005-5-5 10:23:00','9')--查询
set nocount on
select A.dt+B.M as 'dt'
,isnull(min(C.A),0) as 'minValue'
from (select distinct left(convert(varchar(100),time_,120),14) 'dt' from test)A
join ( select '0' as 'M' union
select '1' union
select '2' union
select '3' union
select '4' union
select '5'
)B on 1>0
left join test C on A.dt+B.M=left(convert(varchar(100),C.time_,120),15)
group by A.dt+B.M--删除测试环境
drop table test--结果
/*
dt minValue
----------------------------- -----------
2005-05-05 10:0 0
2005-05-05 10:1 3
2005-05-05 10:2 4
2005-05-05 10:3 4
2005-05-05 10:4 6
2005-05-05 10:5 1
*/
union
select '2005-05-05 10:0',0 这样吗??
--create table test(id int identity(1,1),Time_ datetime,A int)
declare @test table(id int identity(1,1),Time_ datetime,A int)
--truncate table testinsert into @test values('2005-5-5 10:33:00','4')
insert into @test values('2005-5-5 10:35:00','6')
insert into @test values('2005-5-5 10:43:00','7')
insert into @test values('2005-5-5 10:13:00','3')
insert into @test values('2005-5-5 10:53:00','1')
insert into @test values('2005-5-5 10:23:00','4')
insert into @test values('2005-5-5 10:33:00','6')
insert into @test values('2005-5-5 10:35:00','4')
insert into @test values('2005-5-5 10:43:00','6')
insert into @test values('2005-5-5 10:13:00','7')
insert into @test values('2005-5-5 10:53:00','8')
insert into @test values('2005-5-5 10:23:00','9')
insert into @test values('2005-5-5 00:00:00','10')select *,left(convert(varchar(100),time_,21),15) from @test
select 'Min(A)'=min(case left(convert(varchar(100),time_,21),15) when '2005-05-05 00:0' then 0
else A end),
min(A),Time_=left(convert(varchar(100),time_,21),15)
from @test
group by left(convert(varchar(100),time_,21),15)/*
Min(A) Time_
----------- ----------- ------------------------------
0 10 2005-05-05 00:0
3 3 2005-05-05 10:1
4 4 2005-05-05 10:2
4 4 2005-05-05 10:3
6 6 2005-05-05 10:4
1 1 2005-05-05 10:5(6 row(s) affected)
*/
create table test(id int identity(1,1),Time_ datetime,A int)
truncate table testinsert into test values('2005-5-5 10:33:00','4')
insert into test values('2005-5-5 10:35:00','6')
insert into test values('2005-5-5 10:43:00','7')
insert into test values('2005-5-5 10:13:00','3')
insert into test values('2005-5-5 10:53:00','1')
insert into test values('2005-5-5 10:23:00','4')
insert into test values('2005-5-5 10:33:00','6')
insert into test values('2005-5-5 10:35:00','4')
insert into test values('2005-5-5 10:43:00','6')
insert into test values('2005-5-5 10:13:00','7')
insert into test values('2005-5-5 10:53:00','8')
insert into test values('2005-5-5 10:23:00','9')
insert into test values('2005-5-5 10:00:00','1')
select G.Time_,G.A from (select M.Time_,MAX(A) as A,M.T from
(select T1.Time_,T2.A,T1.T from (select T.Time_,count(*) as T from (select left(convert(varchar(100),time_,21),15) as Time_, min(A) as A from test group by left(convert(varchar(100),time_,21),15)
union
select '2005-05-05 10:0'as Time_ ,0 as A) T
group by T.Time_) T1,
(select left(convert(varchar(100),time_,21),15) as Time_, min(A) as A from test group by left(convert(varchar(100),time_,21),15)
union
select '2005-05-05 10:0'as Time_ ,0 as A) T2
where T1.Time_=T2.Time_) M
group by M.Time_,M.T) G
20,120和21,121並不是一样的哦,set nocount on
declare @data datetime
set @data='2005-5-5 10:23:00'
select convert(varchar,@data,20),convert(varchar,@data,21)
結果:
2005-05-05 10:23:00 2005-05-05 10:23:00.000
21,121是有毫秒的
from test
group by left(convert(varchar(100),time_,21),15)
union all
select '2005-05-05 10:0','0'
order by timer
就用红茶的 Case就可以了沙!!
--->沙子,错了,我是抹茶,我从来不喝红茶,我喝Green Tea:-)按照left(convert(varchar(100),time_,112),15)进行group by 取A的Min值,
而且对于时间中缺少的比如2005-05-05 10:0的Min值取为○;-->楼主要这样的结构为了什么,讲讲你的实际应用,说不定可以简化语句呢?