Oracle数据库-如何有条件地从结果中选择两行中的一行

这里有一个选择:

SQL> with test (cdate, amount, flag) as
  2  (select date '2018-09-12', 191, 1 from dual union all
  3   select date '2018-09-12', 190, 0 from dual union all
  4   select date '2018-09-13', 187, 0 from dual union all
  5   select date '2018-09-14', 200, 1 from dual union all
  6   select date '2018-09-14', 201, 0 from dual
  7  )
  8  select cdate, amount, flag
  9  from (select cdate, amount, flag,
 10               row_number() over (partition by cdate order by flag desc) rn
 11        from test
 12       )
 13  where rn = 1;

CDATE          AMOUNT       FLAG
---------- ---------- ----------
12/09/2018        191          1
13/09/2018        187          0
14/09/2018        200          1

SQL>

您可以这样尝试:

select * from <<table>> outer where outer.flag= 1 
and exists  (select * from <<table>> inner where inner.flag = 0 and inner.date=outer.date) 
UNION 
select * from <<table>> outer where outer.flag= 0 and 
not exists  (select * from <<table>> inner where inner.flag = 1 and inner.date=outer.date) ;

我想试试这个:

SELECT date, amount, flag
FROM tableName
HAVING flag = MAX(flag)
GROUP BY date, amount,flag

SELECT * FROM flags WHERE flag = 1
UNION 
SELECT * FROM flags WHERE flag = 0 
AND flagdate NOT IN (SELECT flagdate FROM flags WHERE flag = 1);

SQLFiddle: http://sqlfiddle.com/#!9/69dc02/2