T-SQL：如何將相應列的行顯示為聚合函式的結果？

June 16, 2020

每個人，

我有一個問題 - 如何顯示與聚合函式檢索到的值相對應的（可能不明確的）列？

與下面的數據集相關，任務是找到平均 Salary 最低的 DepartmentID。

以下子查詢用法檢索錯誤： “列 ‘Initial.DepartmentID’ 在選擇列表中無效，因為它既不包含在聚合函式中，也不包含在 GROUP BY 子句中。”

我在這裡研究了其他主題，它應該基於 DepartmentID 的模糊性來顯示，以防多個部門的最低平均工資相同。

SELECT DepartmentID, MIN(AverageSalaries) FROM (
           SELECT DepartmentID, AVG(Salary) AS AverageSalaries FROM Employees
           GROUP BY DepartmentID )
           AS [Initial]

通過從上述查詢中省略 DepartmentID，歧義消失了，並且顯示了最低平均工資：

SELECT MIN(AverageSalaries) FROM (
           SELECT DepartmentID, AVG(Salary) AS AverageSalaries FROM Employees
           GROUP BY DepartmentID )
           AS [Initial]

有沒有辦法讓 DepartmentID 在其旁邊顯示相應的最低平均工資，使用子查詢而不是自加入？在這種情況下一般如何進行？

USE [TestDatabase]
GO
/****** Object:  Table [dbo].[Employees]    Script Date: 12.6.2020 г. 20:05:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
   [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
   [FirstName] [varchar](50) NOT NULL,
   [LastName] [varchar](50) NOT NULL,
   [MiddleName] [varchar](50) NULL,
   [JobTitle] [varchar](50) NOT NULL,
   [DepartmentID] [int] NOT NULL,
   [ManagerID] [int] NULL,
   [HireDate] [smalldatetime] NOT NULL,
   [Salary] [money] NOT NULL,
   [AddressID] [int] NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
   [EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employees] ON 
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (1, N'Guy', N'Gilbert', N'R', N'Production Technician', 7, 16, CAST(N'1998-07-31T00:00:00' AS SmallDateTime), 12500.0000, 166)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (2, N'Kevin', N'Brown', N'F', N'Marketing Assistant', 4, 6, CAST(N'1999-02-26T00:00:00' AS SmallDateTime), 13500.0000, 102)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (3, N'Roberto', N'Tamburello', NULL, N'Engineering Manager', 1, 12, CAST(N'1999-12-12T00:00:00' AS SmallDateTime), 43300.0000, 193)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (4, N'Rob', N'Walters', NULL, N'Senior Tool Designer', 2, 3, CAST(N'2000-01-05T00:00:00' AS SmallDateTime), 29800.0000, 155)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (5, N'Thierry', N'D''Hers', N'B', N'Tool Designer', 2, 263, CAST(N'2000-01-11T00:00:00' AS SmallDateTime), 25000.0000, 40)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (6, N'David', N'Bradley', N'M', N'Marketing Manager', 5, 109, CAST(N'2000-01-20T00:00:00' AS SmallDateTime), 37500.0000, 199)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (7, N'JoLynn', N'Dobney', N'M', N'Production Supervisor', 7, 21, CAST(N'2000-01-26T00:00:00' AS SmallDateTime), 25000.0000, 275)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (8, N'Ruth', N'Ellerbrock', N'Ann', N'Production Technician', 7, 185, CAST(N'2000-02-06T00:00:00' AS SmallDateTime), 13500.0000, 108)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (9, N'Gail', N'Erickson', N'A', N'Design Engineer', 1, 3, CAST(N'2000-02-06T00:00:00' AS SmallDateTime), 32700.0000, 22)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (10, N'Barry', N'Johnson', N'K', N'Production Technician', 7, 185, CAST(N'2000-02-07T00:00:00' AS SmallDateTime), 13500.0000, 285)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (11, N'Jossef', N'Goldberg', N'H', N'Design Engineer', 1, 3, CAST(N'2000-02-24T00:00:00' AS SmallDateTime), 32700.0000, 214)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (12, N'Terri', N'Duffy', N'Lee', N'Vice President of Engineering', 1, 109, CAST(N'2000-03-03T00:00:00' AS SmallDateTime), 63500.0000, 209)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (13, N'Sidney', N'Higa', N'M', N'Production Technician', 7, 185, CAST(N'2000-03-05T00:00:00' AS SmallDateTime), 13500.0000, 73)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (14, N'Taylor', N'Maxwell', N'R', N'Production Supervisor', 7, 21, CAST(N'2000-03-11T00:00:00' AS SmallDateTime), 25000.0000, 82)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (15, N'Jeffrey', N'Ford', N'L', N'Production Technician', 7, 185, CAST(N'2000-03-23T00:00:00' AS SmallDateTime), 13500.0000, 156)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (16, N'Jo', N'Brown', N'A', N'Production Supervisor', 7, 21, CAST(N'2000-03-30T00:00:00' AS SmallDateTime), 25000.0000, 70)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (17, N'Doris', N'Hartwig', N'M', N'Production Technician', 7, 185, CAST(N'2000-04-11T00:00:00' AS SmallDateTime), 13500.0000, 144)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (18, N'John', N'Campbell', N'T', N'Production Supervisor', 7, 21, CAST(N'2000-04-18T00:00:00' AS SmallDateTime), 25000.0000, 245)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (19, N'Diane', N'Glimp', N'R', N'Production Technician', 7, 185, CAST(N'2000-04-29T00:00:00' AS SmallDateTime), 13500.0000, 184)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (20, N'Steven', N'Selikoff', N'T', N'Production Technician', 7, 173, CAST(N'2001-01-02T00:00:00' AS SmallDateTime), 9500.0000, 104)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (21, N'Peter', N'Krebs', N'J', N'Production Control Manager', 8, 148, CAST(N'2001-01-02T00:00:00' AS SmallDateTime), 24500.0000, 11)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (22, N'Stuart', N'Munson', N'V', N'Production Technician', 7, 197, CAST(N'2001-01-03T00:00:00' AS SmallDateTime), 10000.0000, 36)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (23, N'Greg', N'Alderson', N'F', N'Production Technician', 7, 197, CAST(N'2001-01-03T00:00:00' AS SmallDateTime), 10000.0000, 18)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (24, N'David', N'Johnson', N'', N'Production Technician', 7, 184, CAST(N'2001-01-03T00:00:00' AS SmallDateTime), 9500.0000, 142)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (25, N'Zheng', N'Mu', N'W', N'Production Supervisor', 7, 21, CAST(N'2001-01-04T00:00:00' AS SmallDateTime), 25000.0000, 278)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (26, N'Ivo', N'Salmre', N'William', N'Production Technician', 7, 108, CAST(N'2001-01-05T00:00:00' AS SmallDateTime), 14000.0000, 165)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (27, N'Paul', N'Komosinski', N'B', N'Production Technician', 7, 87, CAST(N'2001-01-05T00:00:00' AS SmallDateTime), 15000.0000, 32)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (28, N'Ashvini', N'Sharma', N'R', N'Network Administrator', 11, 150, CAST(N'2001-01-05T00:00:00' AS SmallDateTime), 32500.0000, 133)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (29, N'Kendall', N'Keil', N'C', N'Production Technician', 7, 14, CAST(N'2001-01-06T00:00:00' AS SmallDateTime), 11000.0000, 257)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (30, N'Paula', N'Barreto de Mattos', N'M', N'Human Resources Manager', 9, 140, CAST(N'2001-01-07T00:00:00' AS SmallDateTime), 27100.0000, 2)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (31, N'Alejandro', N'McGuel', N'E', N'Production Technician', 7, 210, CAST(N'2001-01-07T00:00:00' AS SmallDateTime), 15000.0000, 274)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (32, N'Garrett', N'Young', N'R', N'Production Technician', 7, 184, CAST(N'2001-01-08T00:00:00' AS SmallDateTime), 9500.0000, 283)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (33, N'Jian Shuo', N'Wang', NULL, N'Production Technician', 7, 135, CAST(N'2001-01-08T00:00:00' AS SmallDateTime), 9500.0000, 160)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (34, N'Susan', N'Eaton', N'W', N'Stocker', 15, 85, CAST(N'2001-01-08T00:00:00' AS SmallDateTime), 9000.0000, 204)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (35, N'Vamsi', N'Kuppa', N'', N'Shipping and Receiving Clerk', 15, 85, CAST(N'2001-01-08T00:00:00' AS SmallDateTime), 9500.0000, 51)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (36, N'Alice', N'Ciccu', N'O', N'Production Technician', 7, 38, CAST(N'2001-01-08T00:00:00' AS SmallDateTime), 11000.0000, 284)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (37, N'Simon', N'Rapier', N'D', N'Production Technician', 7, 7, CAST(N'2001-01-09T00:00:00' AS SmallDateTime), 12500.0000, 64)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (38, N'Jinghao', N'Liu', N'K', N'Production Supervisor', 7, 21, CAST(N'2001-01-09T00:00:00' AS SmallDateTime), 25000.0000, 55)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (39, N'Michael', N'Hines', N'T', N'Production Technician', 7, 182, CAST(N'2001-01-10T00:00:00' AS SmallDateTime), 14000.0000, 168)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (40, N'Yvonne', N'McKay', N'S', N'Production Technician', 7, 159, CAST(N'2001-01-10T00:00:00' AS SmallDateTime), 10000.0000, 107)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (41, N'Peng', N'Wu', N'J', N'Quality Assurance Supervisor', 13, 200, CAST(N'2001-01-10T00:00:00' AS SmallDateTime), 21600.0000, 39)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (42, N'Jean', N'Trenary', N'E', N'Information Services Manager', 11, 109, CAST(N'2001-01-12T00:00:00' AS SmallDateTime), 50500.0000, 194)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (43, N'Russell', N'Hunter', NULL, N'Production Technician', 7, 74, CAST(N'2001-01-13T00:00:00' AS SmallDateTime), 11000.0000, 258)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (44, N'A. Scott', N'Wright', NULL, N'Master Scheduler', 8, 148, CAST(N'2001-01-13T00:00:00' AS SmallDateTime), 23600.0000, 172)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (45, N'Fred', N'Northup', N'T', N'Production Technician', 7, 210, CAST(N'2001-01-13T00:00:00' AS SmallDateTime), 15000.0000, 282)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (46, N'Sariya', N'Harnpadoungsataya', N'E', N'Marketing Specialist', 4, 6, CAST(N'2001-01-13T00:00:00' AS SmallDateTime), 14400.0000, 106)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (47, N'Willis', N'Johnson', N'T', N'Recruiter', 9, 30, CAST(N'2001-01-14T00:00:00' AS SmallDateTime), 18300.0000, 99)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (48, N'Jun', N'Cao', N'T', N'Production Technician', 7, 38, CAST(N'2001-01-15T00:00:00' AS SmallDateTime), 11000.0000, 197)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (49, N'Christian', N'Kleinerman', N'E', N'Maintenance Supervisor', 14, 218, CAST(N'2001-01-15T00:00:00' AS SmallDateTime), 20400.0000, 118)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (50, N'Susan', N'Metters', N'A', N'Production Technician', 7, 184, CAST(N'2001-01-15T00:00:00' AS SmallDateTime), 9500.0000, 224)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (51, N'Reuben', N'D''sa', N'H', N'Production Supervisor', 7, 21, CAST(N'2001-01-16T00:00:00' AS SmallDateTime), 25000.0000, 249)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (52, N'Kirk', N'Koenigsbauer', N'J', N'Production Technician', 7, 123, CAST(N'2001-01-16T00:00:00' AS SmallDateTime), 10000.0000, 250)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (53, N'David', N'Ortiz', N'J', N'Production Technician', 7, 18, CAST(N'2001-01-16T00:00:00' AS SmallDateTime), 12500.0000, 267)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (54, N'Tengiz', N'Kharatishvili', N'', N'Control Specialist', 12, 90, CAST(N'2001-01-17T00:00:00' AS SmallDateTime), 16800.0000, 129)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (55, N'Hanying', N'Feng', N'P', N'Production Technician', 7, 143, CAST(N'2001-01-17T00:00:00' AS SmallDateTime), 14000.0000, 182)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (56, N'Kevin', N'Liu', N'H', N'Production Technician', 7, 210, CAST(N'2001-01-18T00:00:00' AS SmallDateTime), 15000.0000, 259)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (57, N'Annik', N'Stahl', N'O', N'Production Technician', 7, 16, CAST(N'2001-01-18T00:00:00' AS SmallDateTime), 12500.0000, 262)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (58, N'Suroor', N'Fatima', N'R', N'Production Technician', 7, 38, CAST(N'2001-01-18T00:00:00' AS SmallDateTime), 11000.0000, 86)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (59, N'Deborah', N'Poe', N'E', N'Accounts Receivable Specialist', 10, 139, CAST(N'2001-01-19T00:00:00' AS SmallDateTime), 19000.0000, 103)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (60, N'Jim', N'Scardelis', N'H', N'Production Technician', 7, 74, CAST(N'2001-01-20T00:00:00' AS SmallDateTime), 11000.0000, 88)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (61, N'Carole', N'Poland', N'M', N'Production Technician', 7, 173, CAST(N'2001-01-20T00:00:00' AS SmallDateTime), 9500.0000, 72)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (62, N'George', N'Li', N'Z', N'Production Technician', 7, 184, CAST(N'2001-01-22T00:00:00' AS SmallDateTime), 9500.0000, 58)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (63, N'Gary', N'Yukish', N'W', N'Production Technician', 7, 87, CAST(N'2001-01-23T00:00:00' AS SmallDateTime), 15000.0000, 80)
GO

您可以使用該over ()子句，它允許您在不使用的情況下返回每行結果中的聚合值group by。您使用創建另一個子查詢over ()，然後將每行的值與最小值進行比較：
SELECT * FROM (
 SELECT DepartmentID, AverageSalaries, MIN(AverageSalaries) OVER () AS MinSalary
 FROM (SELECT DepartmentID, AVG(Salary) AS AverageSalaries
   FROM Employees
   GROUP BY DepartmentID) AS Averages
) AS RunningMinimums
WHERE AverageSalaries=MinSalary
此查詢將返回：
DepartmentID  AverageSalaries  MinSalary
----------------------------------------
15            9250,0000        9250,0000

引用自：https://dba.stackexchange.com/questions/269049

T-SQL：如何將相應列的行顯示為聚合函式的結果？

相關問答

查詢沒有兩個相等的“product_name”的“Order_num”

外部查詢是 UNION 一部分的 JSON 路徑的子查詢導致字元串而不是 JSON

查詢獲取數據庫的物理文件名

線上圖書中的自聯接範例審查

SQL 多選與 where 條件的表現真的很差

克服子查詢返回超過 1 個結果