Question

我有几栏数据基:

SightingID, PA1, PA2, Area, PlotID.

不同的电离层扰动(与PA1、PA2和相关的区域)都有相同的定位。我想从几个条件中挑选出每个PlotID(即一行)一个SightingID(即获得一个拥有独特PlotID的DF)。

如果同一地有几处SightingID,则选择SightingID:

First selecting the SightingID which have PA1=O, if there are several SightingID with PA1=0 with the same PlotID
then select the one with the maximum value for Area.
If for one PlotID, there is no SightingID with PA1=0, just take the one with the maximum value for Area.

我来了。

SELECT SightingID, PA1, PA2, PlotID, MAX(Area) FROM DF GROUP BY PlotID

我获得了与SightingID不同的PlotID,后者具有最高的地区价值,但我不知道如何纳入我的第一个条件。

我知道我应当使用<代码>。区别fonction,但我不知道如何与它一起创造条件。如果它有点混淆,但如果有人能帮助我,那将是巨大的。增编

Answer 1

Group By can be used instead of distinct. Depending on the SQL engine, it may even rewrite the query plan to be the same.

以下两个问题在理论上相同:

SELECT DISTINCT a, b FROM foo;

and

SELECT a, b FROM foo GROUP BY a, b;

然而,如果你重新努力达到最大价值,你需要使用小组。我不敢肯定你的问题是什么。

Answer 2

它没有完全清楚你想要什么。如果你想选择与最大区域相关的SightingID,但希望与PA1=0记录,首先就是你如何这样做。

SELECT 
  case when maxPlotZero.Area is null then
          maxPlot.SightingID
        else 
          maxPlotZero.SightingID
  end SightingID

FROM 
 DF
 LEFT JOIN

 (

   SELECT 


         PlotID, 
         MAX(Area) Area
   FROM 
           DF
    WHERE
        PA1=0

    GROUP BY 
        PlotID
)  maxPlotZero

ON df.PlotId= maxPlotZero.PlotID
   and df.Area= maxPlotZero.Area
LEFT JOIN

(

SELECT 



         PlotID, 
         MAX(Area) 
FROM 
       DF

GROUP BY 
    PlotID
)  maxPlot

  ON df.PlotId= maxPlot.PlotID
   and df.Area= maxPlot.Area

友情链接