从每个组中选择第一行，并使用ORDER BY多于一列 [英] Selecting the first row from each group, with ORDER BY more than one column

问题描述

我有一个表 T ，其中列 x ， y ， a ， b 使得

  SELECT x，y，a，b FROM T 
 ORDER BY x，y，a，b 
  

给了我下面的表格

  x | y | a | b 
 x1 | y1 | a1 | b1 
 x1 | y1 | a1 | b2 
 x1 | y1 | a2 | b1 
 x1 | y2 | a1 | b1 
 x1 | y2 | a1 | b2 
 x1 | y2 | a2 | b1 
  

我如何得到每个x，y组的第一行？也就是说，我将如何得到下面的表格

  x | y | a | b 
 x1 | y1 | a1 | b1 
 x1 | y2 | a1 | b1 
  

---

下面是第二个例子： T这样

  x | y | a | b 
 x1 | y1 | a1 | b3 
 x1 | y1 | a1 | b4 
 x1 | y1 | a2 | b1 
 x1 | y1 | a2 | b2 
 x1 | y2 | a1 | b3 
 x1 | y2 | a1 | b4 
 x1 | y2 | a2 | b1 
 x1 | y2 | a2 | b2 
  

我预计会得到

  x | y | a | b 
 x1 | y1 | a1 | b3 
 x1 | y2 | a1 |考虑SQL 2005或更高版本：
 
 
 
 
 
  SELECT T1.X，
 T1.Y，
 T1.A，
 T1.B 
 
（SELECT X，
 Y，
 A，
 B，
 ROW_NUMBER（）OVER（分区BY X，Y顺序A，B）AS RowNum 
 FROM T 
）T1 
 WHERE T1.RowNum = 1 
  
 
I have a table T with columns x,y,a,b such that 
SELECT x,y,a,b FROM T
ORDER BY x,y,a,b
gives me the following table
 x    | y    | a    | b
 x1   | y1   | a1   | b1
 x1   | y1   | a1   | b2
 x1   | y1   | a2   | b1
 x1   | y2   | a1   | b1
 x1   | y2   | a1   | b2
 x1   | y2   | a2   | b1
How would I get the first row of each x,y group? That is, how would I get the following table
 x    | y    | a    | b
 x1   | y1   | a1   | b1
 x1   | y2   | a1   | b1




Here is a second example: For a table T such that
 x    | y    | a    | b
 x1   | y1   | a1   | b3
 x1   | y1   | a1   | b4
 x1   | y1   | a2   | b1
 x1   | y1   | a2   | b2
 x1   | y2   | a1   | b3
 x1   | y2   | a1   | b4
 x1   | y2   | a2   | b1
 x1   | y2   | a2   | b2
I am expecting to get
 x    | y    | a    | b
 x1   | y1   | a1   | b3
 x1   | y2   | a1   | b3

解决方案
Considering SQL 2005 or Above:
SELECT T1.X,
       T1.Y,
       T1.A,
       T1.B
  FROM
      (SELECT X,
              Y,
              A,
              B,
              ROW_NUMBER() OVER (Partition BY X,Y Order By A,B) AS RowNum
         FROM T
     ) T1
WHERE T1.RowNum = 1


                        
这篇关于从每个组中选择第一行，并使用ORDER BY多于一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！