获取不在两个位置的数据 [英] fetch data not in both locations

问题描述

我有三张下面提到的表格

 bank_id bank_name 
   -    ----- ---------  
  1  sbi 
  2  icici 
  3  hdfc 
  4  rbs 
  5  hsbc 

location

 location_id location_city 
   -    --------- ------------  
  1  NY 
  2  Colorado 
  3  gzb 
  4  london 
  5  bankok 

bank_location

 bank_id location_id 
   -     -  ------  
  1   1  
  1   2  
  1   3  
  1   4  
  1   5  
  3   2  
  3   4  
  4   1  
  5   2  
  5   5  

找到在德里或诺伊达有分行的银行，但两者都没有。







i试过如下查询：







< pre lang =SQL> 选择 bank_name，location_city 来自
（ SELECT distinct a.bank_name，b.location_city
FROM 银行a，地点b，bank_location c
WHERE a.bank_id = c.bank_id
AND b.location_id = c.location_id）
其中 location_city = ' DELHI'
或 location_CITY = ' NOIDA'

获取输出：

 bank_name location_city 
   -    ------ -------------  
汇丰DELHI 
 HDFC NOIDA 
汇丰NOIDA 
 SBI DELHI 
 SBI NOIDA 

HERE SBI有这两个地点，但我需要获取bank_names，但两者都没有地点。



帮我解决



提前感谢

解决方案

此查询将给出您的答案

  DECLARE   @ Bank   TABLE （bank_name  VARCHAR （ 20 ），location_city  VARCHAR （ 30 ））
  INSERT   INTO   @ Bank （bank_name，location_city）
  VALUES （'  HSBC'，'  DELHI'），（'  HDFC' ，'  NOIDA'），（'  HSBC'，'  NOIDA'）， （'  SBI'，'  DELHI'），（'  SBI'，< span class =code-string>'  NOIDA'）
 
  SELECT  *  FROM   @ Bank   WHERE  bank_name  NOT   IN  
（ SELECT  T.bank_name  FROM （ SELECT  bank_name，location_city，ROW_NUMBER（） OVER （ PARTITION   BY  bank_name  ORDER   BY  bank_name） 行'  FROM   @Bank ）T 
  WHERE  T.Row> 1）

您好，



请检查以下代码

 < span class =code-keyword> DECLARE   @ BankDtks   TABLE （bank_name  VARCHAR （ 20 ），location_city  VARCHAR （ 30 ））
  INSERT   INTO   @ BankDtks （bank_name，location_city）
  VALUES  （'  HSBC'，'  DELHI'），（'  HDFC'，< span class =code-string>'  NOIDA'），（'  HSBC'，'  NOIDA'），（< span class =code-string>'  SBI'，'  DELHI'），（'  SBI'，'  NOIDA'）
 
  SELECT  bank_name，location_city 
  FROM   @ BankDtks  
  WHERE  location_city  IN （ '  DELHI'，'  NOIDA'）
  AND  bank_name  NOT  < span class =code-keyword> IN （ SELECT  bank_name  FROM   @ BankDtks  
  WHERE  location_city = '  DELHI' 
  AND  bank_name  IN （  SELECT  bank_name  FROM   @ BankDtks  
  WHERE  location_city = '  NOIDA'））

问候，

GVPrabu

i have three tables mentioned below bank

bank_id bank_name 
------- --------- 
1 sbi 
2 icici 
3 hdfc 
4 rbs 
5 hsbc 

location

location_id location_city 
----------- ------------ 
1 NY 
2 Colorado 
3 gzb 
4 london 
5 bankok 

bank_location

bank_id location_id
---- ------ 
1 1 
1 2 
1 3 
1 4 
1 5 
3 2 
3 4 
4 1 
5 2 
5 5

Find the bank which has branch in either delhi or Noida but not in both.



i have tried like below query:

select bank_name,location_city from
 (SELECT distinct a.bank_name, b.location_city
                    FROM bank a, location b, bank_location c
                   WHERE a.bank_id = c.bank_id
                         AND b.location_id = c.location_id)
                         where location_city='DELHI'
                         or location_CITY='NOIDA'

getting output like:

bank_name  location_city
--------   -------------
HSBC         DELHI
HDFC         NOIDA
HSBC         NOIDA
SBI          DELHI
SBI          NOIDA

HERE SBI having both locations,but i need fetch bank_names which are not in both locations.

help me to solve

thanks in advance

解决方案

This Query will give your answer

DECLARE @Bank TABLE(bank_name  VARCHAR(20), location_city VARCHAR(30))
INSERT INTO @Bank(bank_name ,location_city)
VALUES('HSBC','DELHI'), ('HDFC','NOIDA'), ('HSBC','NOIDA'), ('SBI','DELHI'), ('SBI','NOIDA')

SELECT * FROM @Bank WHERE bank_name NOT IN
(SELECT T.bank_name FROM (SELECT bank_name,location_city,ROW_NUMBER()OVER(PARTITION BY bank_name ORDER BY bank_name)'Row' FROM @Bank)T
WHERE T.Row>1)

Hi ,

Check the following Code

DECLARE @BankDtks TABLE(bank_name  VARCHAR(20), location_city VARCHAR(30))
INSERT INTO @BankDtks(bank_name ,location_city)
VALUES('HSBC','DELHI'), ('HDFC','NOIDA'), ('HSBC','NOIDA'), ('SBI','DELHI'), ('SBI','NOIDA')

SELECT bank_name,location_city 
FROM @BankDtks
WHERE location_city IN('DELHI','NOIDA')
AND bank_name NOT IN(SELECT bank_name FROM @BankDtks 
				 WHERE location_city = 'DELHI' 
				 AND bank_name IN (SELECT bank_name FROM @BankDtks 
							    WHERE location_city = 'NOIDA'))

Regards,
GVPrabu

这篇关于获取不在两个位置的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！