求一个sql语句，请大家帮助

Question

我有个表
time            user_type        countnum
2008-06-23 09     1                1
2008-06-23 09     2                1
2008-06-23 10     1                5
2008-06-23 10     2                1
2008-06-24 08     1                4
2008-06-24 14     2                1
2008-06-24 17     1                1
2008-06-24 20     2                1
2008-06-25 09     1                5
2008-06-25 09     2                1
2008-06-25 11     1                4
2008-06-25 13     2                6我想做个统计，结果如下表 (a 表示user_type=1,b 表示user_type=2) 就是要统计每个时间段内的countnum数目。日期      00~06(a)  00~06(b)  06~12(a) 06~12(a)  12~16(a) 12~16(a) 16~20(a) 16~20(a)  20~00(a)  20~00(a)       
2008-6-23    0 0    8     
2008-6-24

Answer 1

就是2008-6-23 00点到06点，a类型的访问人数是0，b类型的访问人数是0，
    2008-6-23 06点到12点，a类型的访问人数是6，b类型的访问人数是2，

Answer 2

SELECT SS,
       SUM("00-06(a)") "00-06(a)",
       SUM("00-06(b)") "00-06(b)",
       SUM("06-12(a)") "06-12(a)",
       SUM("06-12(b)") "06-12(b)",
       SUM("12-18(a)") "12-18(a)",
       SUM("12-18(b)") "12-18(b)",
       SUM("18-24(a)") "18-24(a)",
       SUM("18-24(b)") "18-24(b)"
  FROM (SELECT SS,
               CASE
                 WHEN CC = 1 AND USER_TYPE = 1 THEN
                  COUNTNUM
                 ELSE
                  0
               END "00-06(a)",
               CASE
                 WHEN CC = 1 AND USER_TYPE = 2 THEN
                  COUNTNUM
                 ELSE
                  0
               END "00-06(b)",
               CASE
                 WHEN CC = 2 AND USER_TYPE = 1 THEN
                  COUNTNUM
                 ELSE
                  0
               END "06-12(a)",
               CASE
                 WHEN CC = 2 AND USER_TYPE = 2 THEN
                  COUNTNUM
                 ELSE
                  0
               END "06-12(b)",
               CASE
                 WHEN CC = 3 AND USER_TYPE = 1 THEN
                  COUNTNUM
                 ELSE
                  0
               END "12-18(a)",
               CASE
                 WHEN CC = 3 AND USER_TYPE = 2 THEN
                  COUNTNUM
                 ELSE
                  0
               END "12-18(b)",
               CASE
                 WHEN CC = 4 AND USER_TYPE = 1 THEN
                  COUNTNUM
                 ELSE
                  0
               END "18-24(a)",
               CASE
                 WHEN CC = 4 AND USER_TYPE = 2 THEN
                  COUNTNUM
                 ELSE
                  0
               END "18-24(b)"
          FROM (SELECT CEIL((TO_NUMBER(TO_CHAR(TIME, 'hh24')) + 1) / 6) CC,
                       TRUNC(TIME) SS,
                       COUNTNUM,
                       USER_TYPE
                  FROM TT))
 GROUP BY SS
 ORDER BY SS;

Answer 3

如果按照上面提供字段的话:修改如下,
SELECT SS,
       SUM(A1) "00-06(a)",
       SUM(B1) "00-06(b)",
       SUM(A2) "06-12(a)",
       SUM(B2) "06-12(b)",
       SUM(A3) "12-16(a)",
       SUM(B3) "12-16(b)",
       SUM(A4) "16-20(a)",
       SUM(B4) "16-20(b)",
       SUM(A5) "20-24(a)",
       SUM(B5) "20-24(b)"
  FROM (SELECT SS,
               CASE
                 WHEN CC IN (1, 2, 3) AND USER_TYPE = 1 THEN
                  COUNTNUM
                 ELSE
                  0
               END A1,
               CASE
                 WHEN CC IN (1, 2, 3) AND USER_TYPE = 2 THEN
                  COUNTNUM
                 ELSE
                  0
               END B1,
               CASE
                 WHEN CC IN (4, 5, 6) AND USER_TYPE = 1 THEN
                  COUNTNUM
                 ELSE
                  0
               END A2,
               CASE
                 WHEN CC IN (4, 5, 6) AND USER_TYPE = 2 THEN
                  COUNTNUM
                 ELSE
                  0
               END B2,
               CASE
                 WHEN CC IN (7, 8) AND USER_TYPE = 1 THEN
                  COUNTNUM
                 ELSE
                  0
               END A3,
               CASE
                 WHEN CC IN (7, 8) AND USER_TYPE = 2 THEN
                  COUNTNUM
                 ELSE
                  0
               END B3,
               CASE
                 WHEN CC IN (9, 10) AND USER_TYPE = 1 THEN
                  COUNTNUM
                 ELSE
                  0
               END A4,
               CASE
                 WHEN CC IN (9, 10) AND USER_TYPE = 2 THEN
                  COUNTNUM
                 ELSE
                  0
               END B4,
               CASE
                 WHEN CC IN (11, 12) AND USER_TYPE = 1 THEN
                  COUNTNUM
                 ELSE
                  0
               END A5,
               CASE
                 WHEN CC IN (11, 12) AND USER_TYPE = 2 THEN
                  COUNTNUM
                 ELSE
                  0
               END B5
          FROM (SELECT CEIL((TO_NUMBER(TO_CHAR(TIME, 'hh24')) + 1) / 2) CC,
                       TRUNC(TIME) SS,
                       COUNTNUM,
                       USER_TYPE
                  FROM TT))
 GROUP BY SS
 ORDER BY SS;

Answer 4

SELECT CEIL((TO_NUMBER(TO_CHAR(TIME, 'hh24')) + 1) / 2) CC,
                       TRUNC(TIME) SS,
                       COUNTNUM,
                       USER_TYPE
                  FROM TT不好意思oracledbalgtu ，上面这句我不知道是什么意思，为啥要加1还要除2？

Answer 5

Try it ..
SQL> select to_char(ttime, 'yyyy-mm-dd') as "time",
  2         sum((case
  3               when to_char(ttime, 'HH24') >= '00' and
  4                    to_char(ttime, 'HH24') < '06' and user_type = 1 then
  5                countnum
  6               else
  7                0
  8             end)) "00~06(a)",
  9         sum((case
 10               when to_char(ttime, 'HH24') >= '00' and
 11                    to_char(ttime, 'HH24') < '06' and user_type = 2 then
 12                countnum
 13               else
 14                0
 15             end)) "00~06(b)",
 16         sum((case
 17               when to_char(ttime, 'HH24') >= '06' and
 18                    to_char(ttime, 'HH24') < '12' and user_type = 1 then
 19                countnum
 20               else
 21                0
 22             end)) "06~12(a)",
 23         sum((case
 24               when to_char(ttime, 'HH24') >= '06' and
 25                    to_char(ttime, 'HH24') < '12' and user_type = 2 then
 26                countnum
 27               else
 28                0
 29             end)) "06~12(b)",
 30         sum((case
 31               when to_char(ttime, 'HH24') >= '12' and
 32                    to_char(ttime, 'HH24') < '16' and user_type = 1 then
 33                countnum
 34               else
 35                0
 36             end)) "12~16(a)",
 37         sum((case
 38               when to_char(ttime, 'HH24') >= '12' and
 39                    to_char(ttime, 'HH24') < '16' and user_type = 2 then
 40                countnum
 41               else
 42                0
 43             end)) "12~16(b)",
 44         sum((case
 45               when to_char(ttime, 'HH24') >= '16' and
 46                    to_char(ttime, 'HH24') < '20' and user_type = 1 then
 47                countnum
 48               else
 49                0
 50             end)) "16~20(a)",
 51         sum((case
 52               when to_char(ttime, 'HH24') >= '16' and
 53                    to_char(ttime, 'HH24') < '20' and user_type = 2 then
 54                countnum
 55               else
 56                0
 57             end)) "16~20(b)",
 58         sum((case
 59               when to_char(ttime, 'HH24') >= '20' and
 60                    to_char(ttime, 'HH24') < '00' and user_type = 1 then
 61                countnum
 62               else
 63                0
 64             end)) "20~00(a)",
 65         sum((case
 66               when to_char(ttime, 'HH24') >= '20' and
 67                    to_char(ttime, 'HH24') < '00' and user_type = 2 then
 68                countnum
 69               else
 70                0
 71             end)) "20~00(b)"
 72    from table tt
131   group by to_char(ttime, 'yyyy-mm-dd');time         00~06(a)   00~06(b)   06~12(a)   06~12(b)   12~16(a)   12~16(b)   16~20(a)   16~20(b)   20~00(a)   20~00(b)
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2008-06-23          0          0          6          2          0          0          0          0          0          0
2008-06-24          0          0          4          0          0          1          1          0          0          0
2008-06-25          0          0          9          1          0          6          0          0          0          0

Answer 6

SELECT   SUBSTR (TIME, 1, 10) "日期",
         SUM (CASE
                 WHEN SUBSTR (TIME, 12, 2) > 0
                 AND SUBSTR (TIME, 12, 2) <= 6
                 AND user_type = 1
                    THEN countnum
                 ELSE 0
              END
             ) "00~06(a)",
         SUM (CASE
                 WHEN SUBSTR (TIME, 12, 2) > 0
                 AND SUBSTR (TIME, 12, 2) <= 6
                 AND user_type = 2
                    THEN countnum
                 ELSE 0
              END
             ) "00~06(b)",
         SUM (CASE
                 WHEN SUBSTR (TIME, 12, 2) > 6
                 AND SUBSTR (TIME, 12, 2) <= 12
                 AND user_type = 1
                    THEN countnum
                 ELSE 0
              END
             ) "06~12(a)",
         SUM (CASE
                 WHEN SUBSTR (TIME, 12, 2) > 6
                 AND SUBSTR (TIME, 12, 2) <= 12
                 AND user_type = 2
                    THEN countnum
                 ELSE 0
              END
             ) "06~12(b)",
         SUM (CASE
                 WHEN SUBSTR (TIME, 12, 2) > 12
                 AND SUBSTR (TIME, 12, 2) <= 16
                 AND user_type = 1
                    THEN countnum
                 ELSE 0
              END
             ) "12~16(a)",
         SUM (CASE
                 WHEN SUBSTR (TIME, 12, 2) > 12
                 AND SUBSTR (TIME, 12, 2) <= 16
                 AND user_type = 2
                    THEN countnum
                 ELSE 0
              END
             ) "12~16(b)",
         SUM (CASE
                 WHEN SUBSTR (TIME, 12, 2) > 16
                 AND SUBSTR (TIME, 12, 2) <= 20
                 AND user_type = 1
                    THEN countnum
                 ELSE 0
              END
             ) "16~20(a) ",
         SUM (CASE
                 WHEN SUBSTR (TIME, 12, 2) > 16
                 AND SUBSTR (TIME, 12, 2) <= 20
                 AND user_type = 2
                    THEN countnum
                 ELSE 0
              END
             ) "16~20(b)",
         SUM (CASE
                 WHEN SUBSTR (TIME, 12, 2) > 20
                 AND SUBSTR (TIME, 12, 2) <= 24
                 AND user_type = 1
                    THEN countnum
                 ELSE 0
              END
             ) "20~00(a)",
         SUM (CASE
                 WHEN SUBSTR (TIME, 12, 2) > 20
                 AND SUBSTR (TIME, 12, 2) <= 24
                 AND user_type = 2
                    THEN countnum
                 ELSE 0
              END
             ) "20~00(b)"
    FROM table1
GROUP BY SUBSTR (TIME, 1, 10)