sql-server - SQL Query Group by Count 和 Left Join 表

Question

我需要你的帮助！我掌握了一些简单的 SQL 技能，但是这个查询让我死了……

我的桌子

现在我想要设备上的 TOP5 工作时间（什么设备的工作时间最长）。

我想要这个输出：

我的查询：

SELECT
   Equipment, EquipmentName, count(Equipment) as Count
FROM
   Operations o
   LEFT JOIN Orders ord ON  ord.Id = o.[Order]
   LEFT OUTER JOIN Equipments e ON ord.Equipment = e.EquipmentNumber
GROUP BY 
   Equipment, EquipmentName
   ORDER BY Count DESC;

另一个问题是我如何显示 o.Worktime？我遇到了 GroupBy 错误...

请帮助我谢谢！

标签： sql-servertsql

Answer 1

你可以试试这个查询：

select equip_nr,
       (select equipmentname from table_equipments where equipmentnr = [to].equip_nr) equip_name,
       sum(timeInMins) / 60.0 Worktime
from (
    select (select equipmentnr from table_orders where id = [to].[order]) equip_nr,
           case when workunittime = 'RH' then worktime * 60 else worktime end timeInMins
    from table_operations [to]
    where exists(select 1 from table_orders
                 where [to].[order] = id
                   and location = '152')
      and [start] >= '2018-07-01 00:00:00.000' and [start] < '2018-08-01 00:00:00.000'
) [to] group by equip_nr

顺便说一句，LEFT JOIN相当于LEFT OUTER JOIN。