根据值 - SQL Server，只返回一行返回的行

时间：2023-02-13 03:35:36

I have a select query that returns data a table with different cases by Id, see below:

我有一个选择查询,通过Id返回具有不同情况的表的数据,请参见下文:

Case 1:

+--------+---------+
| RowNum |  Size   |
+--------+---------+
|      1 | large   |
+--------+---------+

Case 2:

+--------+---------+
| RowNum |  Size   |
+--------+---------+
|      1 | small   |
|      2 | x-large |
+--------+---------+

Case 3:

+--------+---------+
| RowNum |  Size   |
+--------+---------+
|      1 | small   |
|      2 | small   |
|      3 | x-large |
|      4 | large   |
+--------+---------+

Case 4:

+--------+---------+
| RowNum |  Size   |
+--------+---------+
|      1 | large   |
|      2 | medium  |
|      3 | large   |
+--------+---------+

Case 5:

+--------+---------+
| RowNum |  Size   |
+--------+---------+
|      1 | small   |
|      2 | x-large |
|      3 | medium  |
|      4 | large   |
+--------+---------+

Case 6, 7, 8.....

案例6,7,8 ......

Note: all the returned table may have different rows and value, so it could be tens cases, and the row number just as a index with no meaning.

注意:所有返回的表可能有不同的行和值,因此它可能是十几个案例,行号只是一个没有意义的索引。

I need only one row returned with the largest size compared to the rows that the table has.

我只需要返回一行,其中最大的大小与表所具有的行相比。

For example:

  1. in Case 1, I only need the 'large' row;

    2. 在案例1中,我只需要'大'行;

  2. in Case 2, I only need the 'x-large' row;

    3. 在案例2中,我只需要'x-large'行;

  3. in Case 3, I only need the 'x-large' row;

    4. 在案例3中,我只需要'x-large'行;

  4. in Case 4, I only need the 'large' row;

    5. 在案例4中,我只需要'大'行;

  5. in Case 5, I only need the 'x-large' row;

    6. 在案例5中,我只需要'x-large'行;

Can anyone help me find a way how to get the result?

任何人都可以帮我找到如何获得结果的方法吗?

Solution can be stored procedures, functions, views, or queries.

解决方案可以是存储过程,函数,视图或查询。

Many thanks!

1 个解决方案

#1

1  

Just to answer the presentation issue regardless of the logic you can use CASE over ORDER BY

只是为了回答演示问题而不管逻辑是什么,你可以使用CASE而不是ORDER BY

select top 1 *
from 
table
order by (
case size
   when 'x-large' then 4 
   when 'large' then 3
   when 'medium' then 2
   when 'small' then 1
   else 0
end
) desc

#1

1  

Just to answer the presentation issue regardless of the logic you can use CASE over ORDER BY

只是为了回答演示问题而不管逻辑是什么,你可以使用CASE而不是ORDER BY

select top 1 *
from 
table
order by (
case size
   when 'x-large' then 4 
   when 'large' then 3
   when 'medium' then 2
   when 'small' then 1
   else 0
end
) desc
标签：

相关文章