带字符串列的枢轴函数返回错误的输出结果

Question

我有两张桌子

class_record_hist，它将存储特定日期的类的出勤项。

student_record_hist将存储特定class.Here的学生出勤率列表transaction_id将是两个表的主键

我的数据是这样的

Working_date                     STUDENT_NAME   STUDENT_STATUS  ATTENDANCE_CODE   TEACHER_UPDATE   TRANSACTION_ID 
-------------                    ------------   --------------  ---------------   --------------   --------------

2017-11-10 10:00:00               JAMES          ABSENT            2344               AB               23453

2017-11-10 10:00:00               PETER          ABSENT            2344               AB               23453

2017-11-10 10:00:00               MURPHY         PRESENT           1001               PR               23453

2017-11-10 10:00:00               MICA           PRESENT           1001               PR               23453

2017-11-10 10:00:00               STELLA         PRESENT           1001               PR               23453

2017-11-10 10:00:00               STEPHEN        PRESENT           1001               PR               23453

2017-11-10 10:00:00               TRACY          PRESENT           1001               PR               23453

2017-11-10 14:00:00               JAMES          PRESENT           1001               PR               23454

2017-11-10 14:00:00               PETER          PRESENT           1001               PR               23454

2017-11-10 14:00:00               MURPHY         PRESENT           1001               PR               23454

2017-11-10 14:00:00               MICA           PRESENT           1001               PR               23454

2017-11-10 14:00:00               STELLA         PRESENT           1001               PR               23454

2017-11-10 14:00:00               STEPHEN        PRESENT           1001               PR               23454

2017-11-10 14:00:00               TRACY          ABSENT            2344               AB               23454

2017-11-11 11:24:00               JAMES          PRESENT           1001               PR               23454

2017-11-11 11:24:00               PETER          PRESENT           1001               PR               23454

2017-11-11 11:24:00               MURPHY         PRESENT           1001               PR               23454

2017-11-11 11:24:00               MICA           PRESENT           1001               PR               23454

2017-11-11 11:24:00               STELLA         PRESENT           1001               PR               23454

2017-11-11 11:24:00               STEPHEN        PRESENT           1001               PR               23454

2017-11-11 11:24:00               TRACY          PRESENT           1001               PR               23454

2017-11-11 14:24:00               JAMES          ABSENT            2344               AB               23454

2017-11-11 14:24:00               PETER          ABSENT            2344               AB               23454

2017-11-11 14:24:00               MURPHY         ABSENT            2344               AB               23454

2017-11-11 14:24:00               MICA           ABSENT            2344               AB               23454

2017-11-11 14:24:00               STELLA         ABSENT            2344               AB               23454

2017-11-11 14:24:00               STEPHEN        PRESENT           1001               PR               23454

2017-11-11 14:24:00               TRACY          PRESENT           1001               PR               23454

我将根据这些记录生成一份报告。

这是我的查询

select * from 
( 
select student_name, student_status,attendance_code, 
working_date,class_id from 
( 
select to_char(a.date_tm,'yyyy-mm-dd hh24:mi:ss')  AS working_date, 
b.student_name,b.student_status,b.attendance_code, 
b.teacher_update,a.transaction_id 
from class_record_hist a,student_record_hist b 
where a.school        = 'DON BOSCO'
and a.building      = 'A1'
and a.class_id   = 'DB-3452' 
and a.date_tm >= to_date('2017-11-10 06:00:00','yyyy-mm-dd hh24:mi:ss') 
and a.date_tm  <= to_date('2017-11-11 18:00:00','yyyy-mm-dd hh24:mi:ss') 
and a.transaction_id      = b.transaction_id 
order by working_date desc,b.student_name asc
)
) 
PIVOT 
( 
max(attendance_code) as code, 
max(student_status) as status
for student_name in ('JAMES','PETER','MURPHY','MICA','STELLA','STEPHEN','TRACY');
) ; 

结果：

WORKING_DATE        CLASS_ID  JAMES_STATUS   JAMES_CODE  PETER_STATUS  PETER_CODE  MURPHY_STATUS  MURPHY_CODE  MICA_STATUS  MICA_CODE   STELLA_STATUS  STELLA_CODE  STEPHEN_STATUS  STEPHEN_CODE  TRACY_STATUS  TRACY_CODE
------------        --------  ------------   ----------  ------------  ----------  -------------  -----------  -----------  ---------   -------------  -----------  --------------  ------------  ------------  ----------


2017-11-10 10:00:00     DB-3452     PRESENT       2344         PRESENT    2344        PRESENT            1001       PRESENT        1001          PRESENT     1001         PRESENT        1001      PRESENT          1001

2017-11-10 14:00:00     DB-3452     PRESENT       1001         PRESENT    1001        PRESENT            1001       PRESENT        1001          PRESENT     1001         PRESENT        1001      PRESENT          2344

2017-11-11 11:24:00     DB-3452     PRESENT       1001         PRESENT    1001        PRESENT            1001       PRESENT        1001          PRESENT     1001         PRESENT        1001      PRESENT          1001

2017-11-11 14:24:00     DB-3452     PRESENT       2344         PRESENT    2344        PRESENT            2344       PRESENT        2344          PRESENT     2344         PRESENT        1001      PRESENT          1001

从上面的结果可以看出数据是不正确的。

说，对于James来说，他缺席了两个会话，这可以从代码2344中看到，但是状态仍然显示为Present.The，整个记录也会出现同样的问题。

我做错什么了？

有什么建议和建议非常感谢？

Answer 1

最小(Student_status)等于最大值(Attendance_code)。

• 缺席== 2344
• 现== 1001

因此相应地调整枢轴

select * 
from ( 
  select 
          student_name, student_status,attendance_code,working_date,class_id 
  from ( 
        your_current_query
        ) d
  ) d2
  PIVOT ( 
        max(attendance_code) as code, 
        min(student_status) as status
        for student_name in ('JAMES','PETER','MURPHY','MICA','STELLA','STEPHEN','TRACY')
         )
; 

SQL Fiddle

Oracle 11g R2架构设置

CREATE TABLE MY_RESULT
    (WORKING_DATE timestamp, STUDENT_NAME varchar2(7), STUDENT_STATUS varchar2(7), ATTENDANCE_CODE int, TEACHER_UPDATE varchar2(2), TRANSACTION_ID int)
;

INSERT ALL 
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'JAMES', 'ABSENT', 2344, 'AB', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'PETER', 'ABSENT', 2344, 'AB', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'MURPHY', 'PRESENT', 1001, 'PR', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'MICA', 'PRESENT', 1001, 'PR', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'STELLA', 'PRESENT', 1001, 'PR', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'STEPHEN', 'PRESENT', 1001, 'PR', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 10:00:00 AM', 'TRACY', 'PRESENT', 1001, 'PR', 23453)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'JAMES', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'PETER', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'MURPHY', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'MICA', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'STELLA', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'STEPHEN', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('10-Nov-2017 02:00:00 PM', 'TRACY', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'JAMES', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'PETER', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'MURPHY', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'MICA', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'STELLA', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'STEPHEN', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 11:24:00 AM', 'TRACY', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'JAMES', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'PETER', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'MURPHY', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'MICA', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'STELLA', 'ABSENT', 2344, 'AB', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'STEPHEN', 'PRESENT', 1001, 'PR', 23454)
    INTO MY_RESULT (WORKING_DATE, STUDENT_NAME, STUDENT_STATUS, ATTENDANCE_CODE, TEACHER_UPDATE, TRANSACTION_ID)
         VALUES ('11-Nov-2017 02:24:00 PM', 'TRACY', 'PRESENT', 1001, 'PR', 23454)
SELECT * FROM dual
;

查询1

select * 
from ( 
  select 
          student_name, student_status,attendance_code,working_date,class_id 
  from ( 
        select * from my_result
        ) d
  ) d2
  PIVOT ( 
        max(attendance_code) as code, 
        min(student_status) as status
        for student_name in ('JAMES','PETER','MURPHY','MICA','STELLA','STEPHEN','TRACY')
         )

结果

|          WORKING_DATE | 'JAMES'_CODE | 'JAMES'_STATUS | 'PETER'_CODE | 'PETER'_STATUS | 'MURPHY'_CODE | 'MURPHY'_STATUS | 'MICA'_CODE | 'MICA'_STATUS | 'STELLA'_CODE | 'STELLA'_STATUS | 'STEPHEN'_CODE | 'STEPHEN'_STATUS | 'TRACY'_CODE | 'TRACY'_STATUS |
|-----------------------|--------------|----------------|--------------|----------------|---------------|-----------------|-------------|---------------|---------------|-----------------|----------------|------------------|--------------|----------------|
| 2017-11-11 14:24:00.0 |         2344 |         ABSENT |         2344 |         ABSENT |          2344 |          ABSENT |        2344 |        ABSENT |          2344 |          ABSENT |           1001 |          PRESENT |         1001 |        PRESENT |
| 2017-11-11 11:24:00.0 |         1001 |        PRESENT |         1001 |        PRESENT |          1001 |         PRESENT |        1001 |       PRESENT |          1001 |         PRESENT |           1001 |          PRESENT |         1001 |        PRESENT |
| 2017-11-10 10:00:00.0 |         2344 |         ABSENT |         2344 |         ABSENT |          1001 |         PRESENT |        1001 |       PRESENT |          1001 |         PRESENT |           1001 |          PRESENT |         1001 |        PRESENT |
| 2017-11-10 14:00:00.0 |         1001 |        PRESENT |         1001 |        PRESENT |          1001 |         PRESENT |        1001 |       PRESENT |          1001 |         PRESENT |           1001 |          PRESENT |         2344 |         ABSENT |

25年前，ANSI标准将“显式”连接语法正式化，现在是您加入这一更改的时候了。例如：

        SELECT
              to_char(c.date_tm, 'yyyy-mm-dd hh24:mi:ss') AS working_date
            , s.student_name
            , s.student_status
            , s.attendance_code
            , s.teacher_update
            , c.transaction_id
        FROM class_record_hist c
        INNER JOIN student_record_hist s ON c.transaction_id = s.transaction_id
        WHERE c.school = 'DON BOSCO'
        AND c.building = 'A1'
        AND c.class_id = 'DB-3452'
        AND c.date_tm >= to_date('2017-11-10 06:00:00', 'yyyy-mm-dd hh24:mi:ss')
        AND c.date_tm <= to_date('2017-11-11 18:00:00', 'yyyy-mm-dd hh24:mi:ss')
        --ORDER BY
            --  working_date DESC
            --, s.student_name ASC

顺便说一下，您不需要在子查询中订购