Oracle SQL - самый последний список записей с максимальным и минимальным количеством

Question

Oracle SQL - самый последний список записей с максимальным и минимальным количеством

Здравствуйте, у меня есть, как я надеюсь, интересная проблема (ну, если быть точным, пара проблем), и я надеюсь, что кто-то может мне помочь. В настоящее время я работаю в SQL в Crystal Reports 2008, который получает свои данные из базы данных Oracle.

Мне нужно получить список сотрудников, которые выполнили определенное минимальное количество тестов, скажем, более 10 тестов, за определенный период времени. Чтобы еще больше скрыть проблему, это должны быть последние 10 тестов, и мне также нужен список каждого уникального теста.

Я начал (см. Первый фрагмент кода и его вывод), который использует функцию ROW NUMBER(), но она показывает только 10 самых последних фрагментов информации для одного штатного номера и не проверяет, были ли проведены необходимые 10 тестов. проводится.

Я также создал другой фрагмент кода (см. Второй фрагмент кода и его вывод), который включает функцию подсчета и показывает только тех сотрудников, которые провели не менее 10 тестов.

То, что мне действительно нужно, показано в таблице "Необходимые результаты":

Итак, мои вопросы / проблемы:

Есть ли способ изменить мой исходный код, чтобы он работал с несколькими штатными номерами?
Есть ли способ объединить обе части кода, чтобы он проверял, было ли выполнено необходимое количество тестов, и отображает только те штатные номера и ссылки на тесты, которые соответствуют требуемым критериям (это предпочтительный вариант)?
Должен ли я отказаться от этой идеи и начать все сначала?

К сожалению, в настоящее время у меня нет доступа администратора к базам данных, и я не могу создать какие-либо новые таблицы данных для хранения каких-либо этих данных, поэтому мне нужно, чтобы это было сделано одним действием SQL, если это возможно.

Я приложил электронную таблицу, которая показывает снимок доступных данных, если это поможет.

Любая помощь, которую вы можете оказать мне, будет оценена.

WITH TESTNUMBER AS
(SELECT TESTER.STAFF_NO, TESTER.TEST_REF, PERIOD. DATE_OF_TEST, TESTSLOT.SLOT_TIME,
ROW NUMBER() OVER (ORDER BY TESTER.STAFF_NO DESC, PERIOD.DATE_OF_TEST DESC, 
TESTSLOT.SLOT_TIME DESC)
AS RowNumber            
FROM dB1.TEST_EVENT TESTER      
INNER JOIN dB1.PERIOD PERIOD ON TESTER.PERIOD_ID=PERIOD.PERIOD_ID
INNER JOIN dB1.TEST_ SLOT_TIME TESTSLOT ON TESTER.SLOT_TIME_ID=TESTSLOT.SLOT_TIME_ID)
WHERE TESTER.STAFF_NO=405405 AND    
(PERIOD.DATE_OF_TEST>={ts '2013-08-01 00:00:00'} AND
PERIOD.DATE_OF_TEST<={ts '2013-08-10 00:00:00'})

SELECT * FROM TESTNUMBER        
WHERE RowNumber BETWEEN 1 AND 10;`

STAFF_NO    TEST_REF    DATE_OF_TEST    SLOT_TIME    
405405  01  05/08/2013  08:40:00
405405  02  05/08/2013  09:40:00
405405  03  05/08/2013  10:40:00
405405  04  06/08/2013  08:40:00
405405  05  06/08/2013  09:40:00
405405  06  06/08/2013  10:40:00
405405  07  06/08/2013  11:40:00
405405  08  07/08/2013  08:40:00
405405  09  07/08/2013  09:40:00
405405  10  07/08/2013  10:40:00

Запрос:

SELECT TESTER.STAFF_NO, count(TESTER.STAFF_NO) AS TOTALTESTS FROM 
FROM dB1.TEST_EVENT TESTER      
INNER JOIN dB1.PERIOD PERIOD ON TESTER.PERIOD_ID=PERIOD.PERIOD_ID
INNER JOIN dB1.TEST_ SLOT_TIME TESTSLOT ON TESTER.SLOT_TIME_ID=TESTSLOT.SLOT_TIME_ID)
WHERE (PERIOD.DATE_OF_TEST>={ts '2013-08-01 00:00:00'} AND PERIOD.DATE_OF_TEST<=
{ts '2013-08-10 00:00:00'})
GROUP BY TESTER.STAFF_NO        
HAVING count(TESTER.STAFF_NO)>=10   
ORDER BY TESTER.STAFF_NO DESC`  

STAFF_NO    TEST_REF        
405405  12          
366255  14

Общий требуемый результат

STAFF_NO    TEST_REF    DATE_OF_TEST    SLOT_TIME    
405405  03  05/08/2013  10:40:00    
405405  04  06/08/2013  08:40:00   
405405  05  06/08/2013  09:40:00    
405405  06  06/08/2013  10:40:00
405405  07  06/08/2013  11:40:00
405405  08  07/08/2013  08:40:00
405405  09  07/08/2013  09:40:00
405405  10  07/08/2013  10:40:00
405405  11  08/08/2013  08:40:00
405405  12  08/08/2013  09:40:00
366255  17  06/08/2013  09:40:00
366255  18  06/08/2013  10:40:00
366255  19  06/08/2013  11:40:00
366255  20  06/08/2013  12:40:00
366255  21  09/08/2013  08:40:00
366255  22  09/08/2013  09:40:00
366255  23  09/08/2013  10:40:00
366255  24  09/08/2013  12:40:00
366255  25  09/08/2013  14:40:00
366255  26  09/08/2013  15:40:00

Извините, если это не выглядит очень профессионально, но я новичок в этом.

Еще раз спасибо за любую помощь.

1

sql oracle crystal-reports-2008

Источник

user3279355 06 фев '14 в 12:10

1 ответ

Решение

Другие вопросы по тегам sql oracle crystal-reports-2008

user3093319 06 фев '14 в 12:57 2014-02-06 12:57 · Accepted Answer · 2014-02-06 12:57

Это всего лишь объединенная версия обоих ваших запросов.

На вопрос № 1

WITH TESTNUMBER AS
(SELECT TESTER.STAFF_NO, TESTER.TEST_REF, PERIOD. DATE_OF_TEST, TESTSLOT.SLOT_TIME,
ROW NUMBER() OVER (PARTITION BY TESTER.STAFF_NO ORDER BY TESTER.STAFF_NO DESC, PERIOD.DATE_OF_TEST DESC, 
TESTSLOT.SLOT_TIME DESC)
AS RowNumber            
FROM dB1.TEST_EVENT TESTER      
INNER JOIN dB1.PERIOD PERIOD ON TESTER.PERIOD_ID=PERIOD.PERIOD_ID
INNER JOIN dB1.TEST_ SLOT_TIME TESTSLOT ON TESTER.SLOT_TIME_ID=TESTSLOT.SLOT_TIME_ID)
WHERE TESTER.STAFF_NO in (405405, 366255) AND    
(PERIOD.DATE_OF_TEST>={ts '2013-08-01 00:00:00'} AND
PERIOD.DATE_OF_TEST<={ts '2013-08-10 00:00:00'})
SELECT * FROM TESTNUMBER        
WHERE RowNumber BETWEEN 1 AND 10
ORDER BY STAFF_NO,RowNumber

Вопрос 2

WITH TESTNUMBER AS
(
SELECT TESTER.STAFF_NO, TESTER.TEST_REF, PERIOD. DATE_OF_TEST, TESTSLOT.SLOT_TIME,
ROW NUMBER() OVER (PARTITION BY TESTER.STAFF_NO ORDER BY TESTER.STAFF_NO DESC, PERIOD.DATE_OF_TEST DESC, 
TESTSLOT.SLOT_TIME DESC)
AS RowNumber            
FROM dB1.TEST_EVENT TESTER      
INNER JOIN dB1.PERIOD PERIOD ON TESTER.PERIOD_ID=PERIOD.PERIOD_ID
INNER JOIN dB1.TEST_ SLOT_TIME TESTSLOT ON TESTER.SLOT_TIME_ID=TESTSLOT.SLOT_TIME_ID)
WHERE TESTER.STAFF_NO in (
                          SELECT TESTER.STAFF_NO FROM 
                          FROM dB1.TEST_EVENT TESTER      
                          INNER JOIN dB1.PERIOD PERIOD
                              ON TESTER.PERIOD_ID = PERIOD.PERIOD_ID
                          INNER JOIN dB1.TEST_ SLOT_TIME TESTSLOT 
                              ON TESTER.SLOT_TIME_ID = TESTSLOT.SLOT_TIME_ID)
                          WHERE (PERIOD.DATE_OF_TEST >= {ts '2013-08-01 00:00:00'} 
                           AND PERIOD.DATE_OF_TEST <= {ts '2013-08-10 00:00:00'})
                          GROUP BY TESTER.STAFF_NO        
                          HAVING count(TESTER.STAFF_NO)>=10
                          )
AND (PERIOD.DATE_OF_TEST>={ts '2013-08-01 00:00:00'} 
AND PERIOD.DATE_OF_TEST<={ts '2013-08-10 00:00:00'}
)
SELECT * FROM TESTNUMBER        
WHERE RowNumber BETWEEN 1 AND 10
ORDER BY STAFF_NO,RowNumber