SQL 编程帮助...使用 count 和 max ..等

Question

我有一系列表格：

• 技术人员（pplSoft、fname、lname、pittID、专业知识、办公电话） 其中 fname 是名字，lname 是姓氏。

• 用户（pplSoft、fname、lname、pittID、办公电话）

• CATEGORIES（类别 ID、类别、描述） 其中此表列出了已提交工单的所有可能类别。

• INVENTORY(机器名称、IP、网络端口、MACADDR、位置 id)

• LOCATIONS（位置 ID、位置、建筑物、注释）

• TICKETS（票号、所有者 pplSoft、提交日期、关闭日期、工作天数、 类别 ID、机器名称、位置、描述）

• ASSIGNMENT（票号、tech pplSoft、分配日期、状态） 其中持有的状态是枚举，可以是：已分配、进行中、委托、成功关闭或关闭不成功。

我的任务是 列出设备名称 具有最大数量的机器的所有名称 2011年12月和2012年1月这两个月出现了问题

。我必须把这个变成SQL。

我可以做这样的事情吗？

select machine_name 
from tickets 
where date_submitted >= '01-DEC-2012' and 'date_submitted <= '31-JAN-2012'  

但我需要计算票数或使用 max 吗？

我如何从这里取得进步？

Answer 1

与任何复杂的 SQL 查询一样，秘诀是将其分解为多个部分，最好是可独立测试的部分。

第一个问题是确定相关时期内每台机器拥有的票数。这里的标准是什么？也许，如果一台机器从 2011 年 11 月开始出现问题并持续到 2011 年 12 月，则应该计算在内；同样，如果问题于 2012 年 1 月开始但于 2012 年 2 月完成，则应计算在内。因此，我们需要：

SELECT machine_name, COUNT(*) AS num_tickets
  FROM tickets
 WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
 GROUP BY machine_name;

如果您决定只考虑提交工单的日期，请将标准调整为引用 date_subscribed 两次；同样，如果仅计算工单完成的日期，则引用 date_completed 两次。请注意，如果一台机器有一个 11 月开始的票证，直到 2 月才解决，上面的查询将对其计数；如果您使用其中任何一种替代方案，则该机器在相关期间没有出现任何问题。

这告诉我们在该时间段内有多少张票可供机器使用。现在我们需要找出最大数量的票证：

SELECT MAX(num_tickets) AS max_tickets
  FROM (SELECT machine_name, COUNT(*) AS num_tickets
          FROM tickets
         WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
         GROUP BY machine_name
       );

现在我们需要选择具有此数量票证的机器名称：

SELECT machine_name
  FROM (SELECT MAX(num_tickets) AS max_tickets
          FROM (SELECT machine_name, COUNT(*) AS num_tickets
                  FROM tickets
                 WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
                 GROUP BY machine_name
               )
        ) AS n
  JOIN (SELECT machine_name, COUNT(*) AS num_tickets
          FROM tickets
         WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
         GROUP BY machine_name
       ) AS m
    ON n.max_tickets = m.num_tickets;

假设 Oracle 支持WITH 子句，这可以被简化（相当大）：

WITH t AS
    (SELECT machine_name, COUNT(*) AS num_tickets
       FROM tickets
      WHERE date_completed >= '01-Dec-2011' AND date_submitted <= '31-Jan-2012'
      GROUP BY machine_name
    )
SELECT t.machine_name
  FROM t
  JOIN (SELECT MAX(num_tickets) AS max_tickets FROM t) AS m
    ON t.num_tickets = m.max_tickets;

警告：我在子查询上使用了“AS 别名”，这是 SQL 标准所支持的。我相信 Oracle 不允许“AS 别名”，只需要在表名后加上“别名”；我不确定这是否也适用于子查询的名称。如果“AS m”和“AS n”符号引起问题，请尝试删除 AS。您可能会发现列重命名“AS num_tickets”等存在类似问题，但我相信 Oracle 确实允许在这种情况下使用 AS。

据推测，这只是一系列问题之一，因为答案似乎不需要除 Tickets 表之外的任何表。据推测，其他问题需要使用其他表格。

Answer 2

您需要使用group by。

select machine_name, count(*) as numMachines
from tickets
where date_submitted >= '01-DEC-2011' and 'date_submitted <= '31-JAN-2012'
group by machine_name
order by numMachines desc

Answer 3

您的查询将为每个问题返回一行。第一步是按机器对结果进行分组，以便每台机器获得一行。然后，您可以添加一个计数列，显示该机器存在多少问题。

要找到问题的最大数量，您需要将查询放入子选择中，以便可以提取最大值。然后，您可以将其用作having 子句中的子选择，以返回具有该最大计数的计算机。

SELECT machine_name, COUNT(machine_name) AS ticket_count
  FROM tickets
  WHERE date_submitted >= '01-DEC-2012' AND date_submitted <= '31-JAN-2012'
  GROUP BY machine_name
  HAVING ticket_count = (
    SELECT MAX(ticket_count) FROM (
      SELECT COUNT(machine_name) AS ticket_count
        FROM tickets
        WHERE date_submitted >= '01-DEC-2012' AND date_submitted <= '31-JAN-2012'
        GROUP BY machine_name
    )
  )

Answer 4

select machine_name, count(machine_name) as totalTicketCount
from tickets 
where date_submitted >= '01-DEC-2012' and 'date_submitted <= '31-JAN-2012' 
Group By machine_name
Order by totalTicketCount DESC