oracle 根据列值有条件地选择行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17342005/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional selecting rows based on a column value
提问by 03Usr
ID RANGE_ID START_DATE END_DATE BAND_TYPE FLAG_LINE
3 1 01/03/2013 31/03/2013 R 1
4 1 01/03/2013 31/03/2013 R 0
5 2 01/03/2013 31/03/2013 R 1
6 2 01/03/2013 31/03/2013 R 0
7 3 01/03/2013 31/03/2013 R 0
8 3 01/03/2013 31/03/2013 N 0
From this table, for each RANGE_ID, I need to select rows using the following conditions: If there are rows with identical columns (apart from the ID field) then only select the row which has FLAG_LINE = 1, if there are identical rows but none of them contain a FLAG_LINE=1 column then select all of them, based on this the query should return the following results:
从这个表中，对于每个 RANGE_ID，我需要使用以下条件选择行：如果有具有相同列的行（除了 ID 字段），那么只选择具有 FLAG_LINE = 1 的行，如果有相同的行但没有其中包含一个 FLAG_LINE=1 列，然后选择所有这些，基于此查询应返回以下结果：
ID RANGE_ID START_DATE END_DATE BAND_TYPE FLAG_LINE
3 1 01/03/2013 31/03/2013 R 1
5 2 01/03/2013 31/03/2013 R 1
7 3 01/03/2013 31/03/2013 R 0
8 3 01/03/2013 31/03/2013 N 0
I tried doing it in chunks: i.e run something similar for each RANGE:
我尝试分块进行：即为每个 RANGE 运行类似的操作：
begin
for x in ( select count(*) cnt
from dual
where exists (
select 1 FROM myTable
WHERE RANGE_ID = 1 AND FLAG_LINE = 1) )
loop
if ( x.cnt = 1 )
then
dbms_output.put_line('flag line exists');
--insert the line with FLAG_LINE = 1 into temp table for this range
else
dbms_output.put_line('does not exist');
--insert the lines into temp table for this range
end if;
end loop;
end;
using this method for each RANGE I populate a temp table and return the results at the end, but this is not quite flexible, is there another way that this can be achieved?
对每个 RANGE 使用这种方法我填充一个临时表并在最后返回结果，但这不是很灵活，还有另一种方法可以实现吗？
Thanks
谢谢
回答by Amit Singh
try like this...
像这样尝试...
Select * from tablename where flag=1
union
(Select * from tablename a where (Select count(*) from tablename b
where a.Range_id=b.RANGE_ID and b.flag=1)<1)
回答by Noel
Try this.
尝试这个。
select * from myTable
where flag_line = 1
or
(range_id, start_date, end_date, band_type) in (
select range_id, start_date, end_date, band_type
from myTable
group by range_id, start_date, end_date, band_type
having max(flag_line) = 0)
回答by mtwaddell
Here's another way to get the results you're wanting
这是获得您想要的结果的另一种方法
SELECT *
FROM info
WHERE flag_line = 1
UNION
SELECT *
FROM info i1
WHERE NOT EXISTS (SELECT NULL
FROM info i2
WHERE i2.range_id = i1.range_id
AND i2.flag_line = 1
)
