如何在单个不同列的基础上显示所有列记录 [英] how to show all columns record on basis of single distinct column

问题描述

如何根据单个不同列显示所有列记录。



单列ID

解决方案

< blockquote>你正在寻找一个小组：

 选择 * 
 来自  table  
  group   by  field1 

偶尔可以使用不同的语句编写：

 field1上sql> 选择  distinct   * 
 来自  table  

但是，在大多数平台上，上述任何一个都不起作用，因为未指定其他列上的行为。 （第一个在MySQL工作，如果你正在使用它。）



你可以获取不同的字段并坚持每次选择一个任意行。 br />


在某些平台上（例如PostgreSQL，Oracle，T-SQL），这可以直接使用窗口函数完成：

 select * 
 from（
 select *，
 row_number（）over（partition by field1）as row_number 
 from table 
）as rows 
 where row_number = 1 

how to show all columns record on basis of single distinct column.

single column ID

解决方案

You're looking for a group by:

select *
from table
group by field1

Which can occasionally be written with a distinct on statement:

select distinct on field1 *
from table

On most platforms, however, neither of the above will work because the behavior on the other columns is unspecified. (The first works in MySQL, if that's what you're using.)

You could fetch the distinct fields and stick to picking a single arbitrary row each time.

On some platforms (e.g. PostgreSQL, Oracle, T-SQL) this can be done directly using window functions:

select *
from (
   select *,
          row_number() over (partition by field1) as row_number
   from table
   ) as rows
where row_number = 1

这篇关于如何在单个不同列的基础上显示所有列记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！