邱明 2015-10-07 09:14 采纳率: 0%
浏览 4277

SQL 按条件统计各个字段次数

 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 


  • 写回答

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函数的问题