时间戳

首页 > 解决方案 > 如何在特殊时间逐日选择数据？

sql - 如何在特殊时间逐日选择数据？

问题描述

我想每天选择数据。条件：数据将在当天从晚上 10:00 到明天晚上 10 点进行截断。例如：

| REGTIME    | T8  | T9 | T10 |
| 2008-12-03 | 879 | 785| 542 |
| 2008-12-04 | 556 | 454| 321 |
| 2008-12-05 | 678 | 122| 212 |
| 2008-12-06 | 887 | 544| 214 |

（2008-12-03 数据将在 2008-12-02 10:00 pm 到 2008-12-03 10:00 pm 显示）...

该查询向我显示了所有日期，但它显示了具有相同值的日期。

SELECT REGTIME2,
       A.T8,T9,T10
FROM 
     ( SELECT                         
              SUM(CASE WHEN sdudent_type = 'AAAA1' THEN 1 ELSE 0 END) AS T8,
              SUM(CASE WHEN sdudent_type = 'AAAA2' THEN 1 ELSE 0 END) AS T9,
              SUM(CASE WHEN sdudent_type = 'AAAA3' THEN 1 ELSE 0 END) AS T10                   
       FROM class_men C     
       WHERE REGTIME >= TO_DATE(:REGTIME_from,'YYYYMMDDHH24MISS')
       AND REGTIME < TO_DATE(:REGTIME_to,'YYYYMMDDHH24MISS')          
) A,
(    
  select distinct
         to_char(REGTIME,'YYYY-MM-DD') AS REGTIME2
  from   class_men
  group by  REGTIME
  order by to_char(REGTIME,'YYYY-MM-DD') desc              
) B

标签： sqloracle

解决方案

当您使用 a CROSS JOIN（使用逗号以旧语法编写）连接两个SELECT子句时，您将得到所有天重复的结果，并且这两个语句之间没有相关性。

你似乎想要这样的东西：

SELECT TRUNC( RegTime ) AS RegTime,                         
       COUNT(CASE WHEN sdudent_type = 'AAAA1' THEN 1 END) AS T8,
       COUNT(CASE WHEN sdudent_type = 'AAAA2' THEN 1 END) AS T9,
       COUNT(CASE WHEN sdudent_type = 'AAAA3' THEN 1 END) AS T10                   
FROM   class_men C
WHERE  REGTIME >= TO_DATE(:REGTIME_from,'YYYYMMDDHH24MISS')
AND    REGTIME < TO_DATE(:REGTIME_to,'YYYYMMDDHH24MISS')
GROUP BY TRUNC( RegTime );

或使用TO_CHAR( RegTime, 'YYYY-MM-DD' )而不是TRUNC( RegTime ).

其中，对于样本数据：

CREATE TABLE class_men ( regTime, sdudent_type ) AS
SELECT DATE '2021-01-01' + INTERVAL '1' MINUTE * LEVEL, 'AAAA1'
  FROM DUAL CONNECT BY LEVEL <= 250 UNION ALL
SELECT DATE '2021-01-01' + INTERVAL '1' MINUTE * LEVEL, 'AAAA2'
  FROM DUAL CONNECT BY LEVEL <= 42 UNION ALL
SELECT DATE '2021-01-01' + INTERVAL '1' MINUTE * LEVEL, 'AAAA3'
  FROM DUAL CONNECT BY LEVEL <= 13 UNION ALL
SELECT DATE '2021-01-02' + INTERVAL '1' MINUTE * LEVEL, 'AAAA1'
  FROM DUAL CONNECT BY LEVEL <= 99 UNION ALL
SELECT DATE '2021-01-02' + INTERVAL '1' MINUTE * LEVEL, 'AAAA2'
  FROM DUAL CONNECT BY LEVEL <= 17 UNION ALL
SELECT DATE '2021-01-02' + INTERVAL '1' MINUTE * LEVEL, 'AAAA3'
  FROM DUAL CONNECT BY LEVEL <= 24 UNION ALL
SELECT DATE '2021-01-03' + INTERVAL '1' MINUTE * LEVEL, 'AAAA1'
  FROM DUAL CONNECT BY LEVEL <= 23 UNION ALL
SELECT DATE '2021-01-03' + INTERVAL '1' MINUTE * LEVEL, 'AAAA3'
  FROM DUAL CONNECT BY LEVEL <= 50;

将输出（范围20210101000000为20210104000000）：

注册时间 | T8 | T9 | T10
:----------------- | --: | -: | --:
2021-01-01 00:00:00 | 250 | 42 | 13
2021-01-03 00:00:00 | 23 | 0 | 50
2021-01-02 00:00:00 | 99 | 17 | 24

（注意：日期格式取决于NLS_DATE_FORMAT会话参数 [除非您使用TO_CHAR]。）

db<>在这里摆弄

推荐阅读