Oracle SQL-同一张表中基于彼此的多处条件 [英] Oracle SQL - Multiplewhere conditions based on each other from the same table
问题描述
我有一个表ledrow，其中有4列.
I have a single table ledrow Which has 4 columns.
我只想选择两行逻辑中都具有(按vnum和comp)行的行.
I want to only select rows where they have (per vnum and comp) rows that are in both sets of logic.
英语说明
例如，在下面的所有数据中，解释预期输出中的行如下:
So for example out of all the data below, explaining the rows in the expected output is as follows:
对于comp 99，我们得到了结果，因为所有vacc数字都属于以下两种逻辑之一:
For comp 99 we have results as all of the vacc numbers fall into either of the sets of logic:
vacc是BETWEEN '1544' AND '1567'或vacc是BETWEEN '3000' AND '3999'
例如，由于以下原因，没有输出comp 77行:
However for example comp 77 rows aren't output because even though:
comp vtype vnum vacc
77 F 1369 3400
77 F 1369 3402
在逻辑的第二部分下，其余行不属于任何行:
Comes under the second part of the logic the rest of the rows don't fall under any:
comp vtype vnum vacc
77 F 1369 1510
77 F 1369 2620
77 F 1369 2620
77 F 1369 2620
77 F 1369 1650
这是因为要成为输出的一部分，我希望每个comp的所有行都必须属于每个comp的逻辑的任何一部分.
This is because to be part of the output I want all rows per comp MUST fall in either parts of the logic per comp.
我尝试了以下代码，但无法获得预期的输出.
I have tried the following code but cannot get my expected output.
当前代码:
SELECT * FROM (
SELECT comp, vtype, vnum, vacc
FROM ledrow
WHERE
((comp = '55' AND vacc BETWEEN '1544' AND '1567') AND (comp = '55' AND (vacc = '3019' OR vacc = '5222')))
OR
((comp = '66' AND vacc BETWEEN '1544' AND '1567') AND (comp = '66' AND (vacc = '3013' OR (vacc BETWEEN '6910' AND '6973'))))
OR
((comp NOT IN ('55', '66') AND vacc BETWEEN '1544' AND '1567') AND (comp NOT IN ('55', '66') AND (vacc BETWEEN '3000' AND '3999')))) outputled
WHERE outputled.vtype = 'F'
预期输出:
comp vtype vnum vacc
99 F 1369 1564
99 F 1369 2610
99 F 1369 2610
99 F 1369 3601
99 F 1369 3600
全表(SELECT * FROM ledrow):
行列
comp vtype vnum vacc
77 F 1369 1510
77 F 1369 2620
77 F 1369 2620
77 F 1369 2620
77 F 1369 3400
77 F 1369 3402
77 F 1369 1650
99 F 1369 1564
99 F 1369 2610
99 F 1369 2610
99 F 1369 3601
99 F 1369 3600
99 I 1369 2450
99 I 1369 2440
99 I 1369 2640
99 J 1369 5430
99 J 1369 2450
99 J 1369 5430
99 J 1369 2455
99 J 1369 5410
99 J 1369 2455
99 J 1369 5410
22 F 1369 2620
22 F 1369 3500
22 F 1369 2495
22 F 1369 1510
22 F 1369 2620
22 F 1369 2620
22 F 1369 2620
22 F 1369 2620
22 F 1369 2620
22 F 1369 2620
22 F 1369 2620
22 F 1369 2620
22 F 1369 2620
22 F 1369 3500
22 F 1369 3500
22 F 1369 3500
22 F 1369 3500
22 F 1369 3500
22 F 1369 3500
22 F 1369 3500
22 F 1369 3500
22 F 1369 3500
22 F 1369 3500
22 F 1369 3500
22 F 1369 2495
22 F 1369 3500
22 F 1369 2495
22 F 1369 3500
22 F 1369 2495
22 F 1369 3500
22 F 1369 2495
22 F 1369 3500
22 F 1369 2495
22 F 1369 3500
22 F 1369 2495
22 F 1369 3500
22 F 1369 2495
22 F 1369 3500
22 F 1369 2495
22 F 1369 3500
22 F 1369 2495
22 I 1369 2450
22 I 1369 2440
22 I 1369 2640
22 J 1369 5520
22 J 1369 5520
22 J 1369 2455
22 J 1369 2450
修改
因此，对于每个vnum和comp，它都需要同时满足WHERE子句中的两个条件.
So for each vnum and comp it needs to satisfy both of the two conditions in the WHERE clause.
对于comp 99，必须同时满足以下两个条件:
So for comp 99 which has to satisfy both the conditions:
((comp NOT IN ('55', '66') AND vacc BETWEEN '1544' AND '1567') AND (comp NOT IN ('55', '66') AND (vacc BETWEEN '3000' AND '3999'))))
推荐答案
如果我了解您要执行的操作，则应该可以:
If I understood what you were trying to do, then this should work :
WHERE comp = '55' AND (vacc between '1544' and '1567' or vacc in('3019','5222')) OR
(comp = '66' AND (vacc between '1544' and '1567' or vacc between '6910' AND '6973' or vacc = '3013')) OR
(comp not IN('55','66') and (vacc BETWEEN '1544' AND '1567' or vacc BETWEEN '3000' AND '3999'))
您对每个第一个条件都指定了两次，这是不必要的，因为它们对于两个接下来的条件都是正确的.
You specified each of the first conditions twice, which is not necessary as they are correct to both of the next conditions.
(comp not IN('55','66') AND Cond1) AND/OR (comp not IN('55','66') AND Cond2)
等于:
comp not IN('55','66') AND (Cond1 AND/OR Cond2)
编辑:您可以尝试执行以下操作:
You can try something like this:
SELECT tt.* FROM (
SELECT t.comp FROM (
SELECT comp,
CASE WHEN comp = '55' AND vacc BETWEEN '1544' AND '1567' THEN 1
WHEN comp = '55' AND vacc in('3019','5222') THEN 2
WHEN comp = '66' AND vacc BETWEEN '1544' AND '1567' THEN 3
WHEN comp = '66' AND (vacc = '3013' OR (vacc BETWEEN '6910' AND '6973')) THEN 4
WHEN comp NOT IN ('55', '66') AND vacc BETWEEN '1544' AND '1567' THEN 5
WHEN comp NOT IN ('55', '66') AND (vacc BETWEEN '3000' AND '3999') THEN 6
ELSE 7
END as ind_col
FROM ledrow
WHERE vtype = 'F') t
GROUP BY t.comp
HAVING (MAX(t.ind_col) = 2 and min(t.ind_col) = 1) OR
(MAX(t.ind_col) = 4 and min(t.ind_col) = 3) OR
(MAX(t.ind_col) = 6 and min(t.ind_col) = 5)) s
INNER JOIN ledrow tt
ON(s.comp = tt.comp)
WHERE tt.vtype = 'F'
这基本上将为每行提供一个指示，第一个条件是1,2，与该条件所满足的部分相对应，第二个条件是3,4，第三个条件是5,6.然后您检查每个组是否只有1 + 2或3 + 4或5 + 6，并且HAVING子句的MAX()和MIN()可以确保该comp满足两者条件.
This will basically give an indication to each row, first condition is 1,2 corresponding to the part of this condition the satisfied , second one is 3,4 and third is 5,6 . Then you check that each group only has 1+2 or 3+4 or 5+6 with the HAVING clause with MAX() and MIN() which will assure that this comp satisfy both conditions.
此解决方案仅适用于同时满足两个条件的行，而不仅满足其中一个条件.如果仅其中之一就足够了，那就将hading子句更改为:
This solution works only for rows that satisfy both conditions, not only one of them. If only one of them is enough, that change the having clause to this:
HAVING (MAX(t.ind_col) = 2 and min(t.ind_col) = 1) OR
(MAX(t.ind_col) = 4 and min(t.ind_col) = 3) OR
(MAX(t.ind_col) = 6 and min(t.ind_col) = 5) OR
(MAX(t.ind_col) = min(t.ind_col) and min(t.ind_col) IN(1,2,3,4,5,6)
这篇关于Oracle SQL-同一张表中基于彼此的多处条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
