从记录中获取最大价值

debugcn 发表于 Dev

艾曼

以下查询计算员工在一家商店工作的天数：

SELECT 
    Outlet.Code,
    COUNT(DISTINCT (LogDate)) AS [Worked Days],
    AccessLog.EmployeeID AS [Employee ID],
    abr AS Outlet,
    GEmp.Name,
    Outlet.Brands
FROM 
    [dbo].[AccessLog]
INNER JOIN 
    dbo.Outlet ON dbo.Outlet.Code = dbo.AccessLog.TerminalID
INNER JOIN 
    dbo.GEmp ON dbo.GEmp.EmpCode = dbo.AccessLog.EmployeeID
WHERE 
    CONVERT(datetime, [LogDate]) BETWEEN '2017/10/01' AND '2017/10/31' 
    AND Outlet.Brands = 'brand1'
    AND [dbo].[AccessLog].InOut = '0'
GROUP BY 
    AccessLog.EmployeeID,
    dbo.Outlet.abr,
    GEmp.Name,
    dbo.Outlet.Brands, dbo.Outlet.Malls, Outlet.Code
ORDER BY 
    Outlet.Malls, GEmp.Name

输出：

Code    Worked Days Employee ID Outlet  Name    Brands
-------------------------------------------------------
20019   16          362573      shop1   john    brand1
20038    3          362573      shop2   john    brand1
20038    5          362574      shop1   mike    brand1
20038    1          362574      shop2   mike    brand1

我希望获得他为每个员工工作更多天的商店，因此所需的输出应该是：

Code    Worked Days Employee ID Outlet  Name    Brands
---------------------------------------------------------
20019   16          362573      shop1   john    brand1
20038    5          362574      shop1   mike    brand1

斯坦尼斯拉夫·昆迪

SELECT TOP 1 WITH TIES
  Outlet.Code,
 COUNT(DISTINCT (LogDate)) AS [Worked Days],
  AccessLog.EmployeeID AS [Employee ID],
  abr AS Outlet,
  GEmp.Name,
  Outlet.Brands
FROM [dbo].[AccessLog]
INNER JOIN dbo.Outlet
  ON dbo.Outlet.Code = dbo.AccessLog.TerminalID
INNER JOIN dbo.GEmp
  ON dbo.GEmp.EmpCode = dbo.AccessLog.EmployeeID
WHERE CONVERT(datetime, [LogDate]) BETWEEN '2017/10/01' AND '2017/10/31' and dbo.GEmp.EmpCode='362573'
AND Outlet.Brands = 'brand1'
AND [dbo].[AccessLog].InOut = '0'
GROUP BY AccessLog.EmployeeID,
         dbo.Outlet.abr,
         GEmp.Name,
         dbo.Outlet.Brands,
         dbo.Outlet.Malls,
         Outlet.Code
ORDER BY ROW_COUNT() OVER (PARTITION BY Outlet.Code ORDER BY [Worked Days] DESC)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。