Question

请帮助我简化和降低以下询问的费用?

我试图使其成为与NOT EXISTS共同相关的分局,但它没有给我任何产出。

请注意,主问和内询问的表格与表1相同。

SELECT *
FROM Table_1 A
WHERE A.Col1              =  abcd 
AND (A.Col2, A.Col3) NOT IN
  (SELECT Col2,
    Col3
  FROM Table_1 B
  WHERE (B.Col4 IN (1,2,3)
  And B.Col5    In ( x , y ))
  OR (B.Col4     = 1 AND B.Col5     =  z  AND B.Col6     =  f )
  ))

Thanks in advance, Savitha

Answer 1

Usually its a lot about trial and error. In addition to using the not exists and outer join, since this is a self-join, it should reduce down to a single table...

SELECT *
FROM Table_1
WHERE col1 =  abcd 
AND NOT
   (col4 IN (1,2,3)
      AND col5 IN ( x , y )
)
AND NOT (col4=1
  AND col5= z 
  AND col6= f 
);

或你可以尝试使用MINUS......

SELECT *
FROM Table_1
WHERE col1 =  abcd 
MINUS
SELECT *
FROM Table_1
WHERE col1 =  abcd 
AND ((col4 IN (1,2,3)
      AND col5 IN ( x , y ))
   OR (col4=1
      AND col5= z 
      AND col6= f )  
);

最有效的解决办法将取决于数据的传播。

但它没有给我任何产出。

你们是否相信有配对网?

Answer 2

Try the following to see if it helps:

SELECT A.*
  FROM (SELECT *
          FROM Table_1
           WHERE A.Col1 =  abcd ) A
LEFT OUTER JOIN (SELECT Col2, Col3, primary_key_column
                   FROM Table_1
                   WHERE (B.Col4 IN (1, 2, 3) AND
                          B.Col5 IN ( x ,  y )) OR
                         (B.Col4 = 1 AND
                          B.Col5 =  z  AND
                          B.Col6 =  f )) B
  ON (B.Col2 = A.Col2 AND B.Col3 = A.Col3)
WHERE B.primary_key_column IS NULL;

你们应该有以下指数:TABLE(1Col1)、TABLE(1Col2、Col3)和TABLE1的主要栏目,不管其可能是什么。

分享和享有。

Answer 3

Cool answer. Shouldn t the following be the same if indexes are used

SELECT A.*
  FROM Table_1 A
LEFT OUTER JOIN (SELECT Col2, Col3, primary_key_column
                   FROM Table_1
                   WHERE (B.Col4 IN (1, 2, 3) AND
                          B.Col5 IN ( x ,  y )) OR
                         (B.Col4 = 1 AND
                          B.Col5 =  z  AND
                          B.Col6 =  f )) B
  ON (B.Col2 = A.Col2 AND B.Col3 = A.Col3)
WHERE A.Col1 =  abcd 
  and B.primary_key_column IS NULL;

友情链接