Oracle SQL-具有最大和最小数量的最新记录列表

Dave-69 发表于 Dev

戴夫69

您好，我希望有一个有趣的问题（准确地说，还有几个问题），我希望有人可以帮助我。我目前正在Crystal Reports 2008中的SQL中工作，该SQL正在从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

抱歉，如果它看起来不是很专业，但我对此并不陌生。

再次感谢任何帮助。

Maheswaran Ravisankar

只是两个查询的合并版本。

对于问题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