SQL Server sql condition multiple condition when column a =! column b BUT with exception

bxgwgixi  于 12个月前  发布在  其他
关注(0)|答案(2)|浏览(85)

How to write query for such case
| Column A | Column B |
| ------------ | ------------ |
| Cell ABC | 1 |
| Cell oo | bb |
| Cell BCD | 2 |
| Cell bb | oo |

I need to meet conditions :

Show me only results of Column A and B when they differs BUT with exception when
Column A = 'oo' and Column B = 'bb' or Column A = 'bb' and Column B = 'oo' i don't want them in my results (they have exception)

Based on this example i expect to see in results only
| | |
| ------------ | ------------ |
| Cell ABC | 1 |
| Cell BCD | 2 |

I tried query

Select * from Table where Column A ! = Column B

but I am stuck with part to exclude when columns have particular values.

b4wnujal

b4wnujal1#

You can try those conditions :

SELECT *
FROM mytable
WHERE (
      columnA <> columnB 
      AND NOT (columnA = 'bb' and columnB = 'oo' 
               or columnA = 'oo' and columnB = 'bb')
      ) OR columnA IS NULL OR columnB IS NULL;

Which, for the sample data:

create table mytable (
  ColumnA   varchar(20),
  ColumnB   varchar(20)
);

insert into mytable values
('ABC', '1'),
('oo',  'bb'),
('oo',  NULL),
(NULL,  'bb'),
(NULL,  NULL),
('BCD', '2'),
('bb',  'oo');

Outputs :

ColumnA ColumnB
ABC     1
oo      null
null    bb
null    null
BCD     2

Demo here

mrphzbgm

mrphzbgm2#

You can use the condition to exclude the records and then negate them with NOT as follows:

SELECT *
FROM   YOUR_TABLE
WHERE  COLUMN_A <> COLUMN_B
AND    NOT ((COLUMN_A = 'oo' AND COLUMN_B = 'bb') 
             OR (COLUMN_A = 'bb' AND COLUMN_B = 'oo'))

相关问题