ID value1 value2 value3 value4 value5 DATETIME_col
A 50 75 0 0 0 2015-10-07 12:27:00.000
A 50 75 0 0 0 2015-10-07 12:29:00.000
A 51 75 0 0 0 2015-10-07 12:39:00.000
B 36 42 29 12 0 2015-10-07 16:58:00.000
B 36 43 30 19 0 2015-10-07 16:57:00.000
B 36 43 30 12 0 2015-10-07 16:56:00.000
C 28 33 29 10 89 2015-10-07 16:54:00.000
C 29 34 29 10 16 2015-10-07 16:55:00.000
C 29 35 29 15 18 2015-10-07 16:56:00.000
C 29 35 30 16 18 2015-10-07 16:57:00.000
D 31 55 34 35 19 2015-10-07 00:10:00.000
D 31 54 34 38 31 2015-10-07 00:11:00.000
D 31 54 34 33 15 2015-10-07 00:12:00.000
条件 在2015-10-07这一天统计 value1>30，value2>30,value3>30,value4<15,value5<40 的结果
结果希望这样显示
ID vcount1 vcount2 vcount3 vcount4 vcount5 DATE_col
A 3 3 0 3 3 2015-10-07
B 3 3 0 2 3 2015-10-07
C 0 4 0 2 3 2015-10-07
D 3 3 3 0 3 2015-10-07
with t as
(
select 'A' as ID ,50 as value1 ,75 as value2 ,0 as value3,0 as value4 ,0 as value5, '2015-10-07 12:27:00.000' as DATETIME_col
union all
select 'A' ,50,75,0,0,0,'2015-10-07 12:29:00.000'
union all
select 'A' ,51,75,0,0,0,'2015-10-07 12:39:00.000'
union all
select 'B',36,42,29,12,0,'2015-10-07 16:58:00.000'
union all
select 'B',36,43,30,19,0,'2015-10-07 16:57:00.000'
union all
select 'B',36,43,30,12,0,'2015-10-07 16:56:00.000'
union all
select 'C',28,33,29,10,89,'2015-10-07 16:54:00.000'
union all
select 'C',29,34,29,10,16,'2015-10-07 16:55:00.000'
union all
select 'C',29,35,29,15,18,'2015-10-07 16:56:00.000'
union all
select 'C',29,35,30,16,18,'2015-10-07 16:57:00.000'
union all
select 'D',31,55,34,35,19,'2015-10-07 00:10:00.000'
union all
select 'D',31,54,34,38,31,'2015-10-07 00:11:00.000'
union all
select 'D',31,54,34,33,15,'2015-10-07 00:12:00.000'
)
select
id
,sum(mark_v1) as vcount1
,sum(mark_v2) as vcount2
,sum(mark_v3) as vcount3
,sum(mark_v4) as vcount4
,sum(mark_v5) as vcount5
,max('2015-10-07 00:11:00.000'::date)
from
(
select
*
,case when value1>30 then 1 else 0 end as mark_v1
,case when value2>30 then 1 else 0 end as mark_v2
,case when value3>30 then 1 else 0 end as mark_v3
,case when value4<15 then 1 else 0 end as mark_v4
,case when value5<40 then 1 else 0 end as mark_v5
from
t
) a
group by id order by id
仅供参考