英文:
How to display the details of the most frequently referenced entry
问题
我有一个与IT帮助台相关的票务表格。每个票务 "ID_TICKET" 都与特定故障相关，并且有一个关联的计算机 "SERIAL_NO_COMPUTER"。
我想要能够计算与每台计算机连接的故障数量，然后显示具有最多故障的计算机的每个单独故障的详细信息 "PROBLEM_DESCRIPTION"。
我已经创建了下面的查询来统计与每台计算机相关的故障数量：
SELECT SERIAL_NO_COMPUTER, COUNT(*) AS OCCURANCES
FROM TICKET
GROUP BY SERIAL_NO_COMPUTER
ORDER BY COUNT(*) DESC;
这为我提供了报告故障最多的计算机，但我不确定如何仅使用一个语句来生成每个问题的详细信息。
英文:
So I have a table which is for tickets within an IT helpdesk. Each Ticket "ID_TICKET" is related to a specific fault and has an associated computer "SERIAL_NO_COMPUTER".
I want to be able to count the number of faults connected with each computer and then display the details of each individual fault "PROBLEM_DESCRIPTION" for the computer with the most faults.
I have created the below to tally the number of faults associated with each machine:
SELECT SERIAL_NO_COMPUTER, COUNT(*) AS OCCURANCES
FROM TICKET
GROUP BY SERIAL_NO_COMPUTER
ORDER BY COUNT(*) DESC;
This provides me with the computer with the most reported faults but I am unsure how to then produce the details for each problem using only one statement.
答案1
得分: 1
- 使用窗口函数在
ORDER BY子句中，根据记录数更改记录的顺序，以收集报告最多故障的计算机的信息。 - 使用
FETCH NEXT 1 ROWS WITH TIES，以收集与最高计数关联的一行记录（此子句考虑ORDER BY子句）。
SELECT TICKET.*
FROM TICKET
ORDER BY COUNT(*) OVER(PARTITION BY SERIAL_NO_COMPUTER) DESC
FETCH NEXT 1 ROW WITH TIES
如果两台计算机具有相同数量的最大故障，此查询应为您提供两台计算机的记录。如果您希望仅获取一台计算机的记录，您需要在ORDER BY子句中添加另一列进行排序（例如计算机序列号）。
SELECT TICKET.*
FROM TICKET
ORDER BY COUNT(*) OVER(PARTITION BY SERIAL_NO_COMPUTER) DESC, SERIAL_NO_COMPUTER
FETCH NEXT 1 ROW WITH TIES
英文:
If you need to gather information of the computer with the most reported faults, instead of aggregating, you can use:
- a window function inside the
ORDER BYclause, that changes the order of your records based on the count of records. FETCH NEXT 1 ROWS WITH TIES, to gather the one row tied on the highest count (this clause takes into consideration theORDER BYclause).
SELECT TICKET.*
FROM TICKET
ORDER BY COUNT(*) OVER(PARTITION BY SERIAL_NO_COMPUTER) DESC
FETCH NEXT 1 ROW WITH TIES
If two pcs have the same amount of maximum faults, this query should give you records from both pcs. If you instead want just records from one pc only, you need to add a further column to order on inside the ORDER BY clause (for example the pc serial).
SELECT TICKET.*
FROM TICKET
ORDER BY COUNT(*) OVER(PARTITION BY SERIAL_NO_COMPUTER) DESC, SERIAL_NO_COMPUTER
FETCH NEXT 1 ROW WITH TIES
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论