求SQL代码 思路

[XJQ_1]

id a b c

1401 144 137 2009-11-17 08:37:38.000
901 142 140 2009-11-17 09:37:38.000
1110 128 125 2009-11-17 19:19:45.000
610 126 128 2009-11-17 20:19:45.000

我想取出0-12点 12点--24点 各自A最大值，结果是

1401 144 137 2009-11-17 08:37:38.000
1110 128 125 2009-11-17 19:19:45.000

[Airch]

0-12的

select  max(a) from table where Convert(varchar(2),datepart(hour,[时间])) <=12

12-24的

select  max(a) from table where Convert(varchar(2),datepart(hour,[时间])) >=12

[Airch]

如果不取12点的，把等于去掉

[子夜__]

select  max(a) from table where Convert(varchar(2),datepart(hour,[时间])) <=12

union all

select  max(a) from table where Convert(varchar(2),datepart(hour,[时间])) >=12 and Convert(varchar(2),datepart(hour,[时间])) <=24

[wag_enu]

declare @t table ([id] int, a int, b int, c datetime)
insert into @t
select 1401,144,137,'2009-11-17 08:37:38.000' union all
select 901,142,140,'2009-11-17 09:37:38.000' union all
select 1110,128,125,'2009-11-17 19:19:45.000' union all
select 610,126,128,'2009-11-17 20:19:45.000'


select * from @t t 
where exists(select 1 from 
		(select max(id) as id from @t 
		group by case when convert(char(10), c, 114)<= '12:00:00:0000' then 'a' else 'b' end
		) t1 where t.id=t1.id)

[mayanly]

[Quote=引用 1 楼 airch 的回复:]
0-12的

SQL code

select max(a) from table where Convert(varchar(2),datepart(hour,[时间])) <=12



12-24的+1

SQL code

select max(a) from table where Convert(varchar(2),datepart(hour,[时间])) >……
[/Quote]

[我已经被打过了]

[Quote=引用 3 楼 wxr0323 的回复:]

SQL code
select max(a) from table where Convert(varchar(2),datepart(hour,[时间])) <=12

union all

select max(a) from table where Convert(varchar(2),datepart(hour,[时间])) >=12 and Convert(varchar(2),……
[/Quote]
+1

[小童]

select convert(varchar(12),c,23),max(a) as zd,px from (
select *,
case when datepart(hour,c)<12 then '上午' else '下午' end as px
from t) as s group by convert(varchar(12),s.c,23),s.px

[广交天下好友]

4楼代码 正解

[新时代新目标新征程]

解决了啊。我来看下有份接吗。。

[tsltsl2008]

4楼正解！~~~~~

[IE11下面经常卡到爆]

1楼的也可以吧
主要用max和DataPart