①利用临时表,先存储按分组和每组最小的数据,再和表连接,查找表所有属性
create table #array (EmployeeID int,min_OrderID int)
insert #array select EmployeeID,min(OrderID) from orders group by EmployeeID
select * from #array
select * from orders,#array
where orders.EmployeeID=#array.EmployeeID
and orders.OrderID=#array.min_OrderID
drop table #array
②用别名分组和每组,再和表连接,查找表所有属性
select *
from orders,
(select EmployeeID,min(OrderID) as min_OrderID from orders group by EmployeeID)
as MinTable Where orders.EmployeeID = MinTable.EmployeeID
and orders.OrderID=MinTable.min_OrderID
order by EmployeeID desc