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
SQL 按条件统计各个字段次数
- 写回答
- 好问题 0 提建议
- 追加酬金
- 关注问题
- 邀请回答
-
1条回答 默认 最新
- chinamobile851116 2023-08-16 11:52关注
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
仅供参考
解决 无用评论 打赏 举报
悬赏问题
- ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
- ¥15 arduino控制ps2手柄一直报错
- ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
- ¥15 求chat4.0解答一道线性规划题,用lingo编程运行,第一问要求写出数学模型和lingo语言编程模型,第二问第三问解答就行,我的ddl要到了谁来求了
- ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
- ¥50 树莓派安卓APK系统签名
- ¥65 汇编语言除法溢出问题
- ¥15 Visual Studio问题
- ¥20 求一个html代码,有偿
- ¥100 关于使用MATLAB中copularnd函数的问题