SQL |返回最小值 |动态多行

2022-01-23 00:00:00 sql subquery sql-server sql-server-2012

我需要创建一个查询，它只返回特定列中具有最小值的行.

I need to create a query that returns only the rows which have the minimum values in a specific column.

我有这个结果(示例):

I have this results (example):

Name  |  Description  | Order
-----------------------------
Name1 |  A            | 1
Name1 |  B            | 2
Name1 |  C            | 3
Name2 |  A            | 1
Name2 |  B            | 2
Name2 |  C            | 3

我想得到这个结果:

Name  |  Description  | Order
-----------------------------
Name1 |  A            | 1
Name2 |  A            | 1

基本上，我只需要选择 order 列中具有最小值的行.

Basically, I need to select only the rows which have the minimum value in the column order.

我被 Query A 之类的查询卡住了:

I'm stuck with queries like Query A:

Select Top 1 *
From table1
Where Name = 'Name1'
Order by Order

或查询B:

Select *
From table1
Where Name = 'Name1'
 and order = (Select min(Order)
              From table1
              Where Name = 'Name1')

我需要的是在单个查询中具有返回多个名称的行的能力，而不是必须对每个名称进行查询.有没有办法将属性值传递给 Query B 示例中的子查询?

What I need is to have in a single query, the capability of returning the rows for multiple names, instead of having to do queries per name. Is there any way, to pass attributes values to the subquery in the example of Query B?

有什么选择?

平台是 SQL Server 2012.

The platform is SQL Server 2012.

推荐答案

如果你想使用一个简单的子查询，你已经接近第二个查询了:

You're close on the second query if you want to use a simple subquery:

Select *
From table1 t1
WHERE [order] = (Select min([Order])
             From table1
             Where Name = t1.Name)

您还可以使用分区函数来查找每个组的第一条"记录:

You can also use partitioning functions to find the "first" record for each group:

SELECT Name, Description, [Order] FROM
    (SELECT *,
        ROW_NUMBER() OVER(ORDER BY [Order] PARTITION BY Name) RowNum
     FROM Table1 
    ) A
WHERE RowNum = 1

相关文章