主页 / 问题 / 1155889
Accepted
wq55
Asked:2020-07-22 17:26:38 +0000 UTC2020-07-22 17:26:38 +0000 UTC

按结果天数拆分

  • 772

需要按天拆分以下样本

   (select  count(*) c from cdrquery t, calend t2 where trunc(to_date(t.data_start_talk1,'YYYY-MM-DD HH24:MI:SS'))=t2.num_day and t.redirect_num1='8800'
   and trunc(to_date(t.data_start_talk1,'YYYY-MM-DD-HH24:MI:SS')) between '01.06.2020' and '30.06.2020'
   and substr(t.data_start_talk1,12,100) between '00:00:00' and '08:59:59'
   and t2.weekend=0 and t.type_output='user-service')

让它看起来像这样

  01.06.2020  5

  02.06.2020  10

  ...

  29.06.2020  0

  30.06.2020  15
sql

1 个回答

  1. Best Answer

    您需要添加到您的请求GROUP BY：

    SELECT
    trunc(TO_DATE(t.data_start_talk1, 'YYYY-MM-DD HH24:MI:SS')) d,
    COUNT(*) c
FROM
    cdrquery   t,
    calend     t2
WHERE
    trunc(TO_DATE(t.data_start_talk1, 'YYYY-MM-DD HH24:MI:SS')) = t2.num_day
    AND t.redirect_num1 = '8800'
    AND trunc(TO_DATE(t.data_start_talk1, 'YYYY-MM-DD-HH24:MI:SS')) BETWEEN '01.06.2020' AND '30.06.2020'
    AND substr(t.data_start_talk1, 12, 100) BETWEEN '00:00:00' AND '08:59:59'
    AND t2.weekend = 0
    AND t.type_output = 'user-service'
GROUP BY
    trunc(TO_DATE(t.data_start_talk1, 'YYYY-MM-DD HH24:MI:SS'))
ORDER BY
    trunc(TO_DATE(t.data_start_talk1, 'YYYY-MM-DD HH24:MI:SS'))
    • 1

相关问题