本文介绍了获取不在两个位置的数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 我有三张下面提到的表格 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试过如下查询: 选择 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',' 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) 您好, 请检查以下代码 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')) 问候, GVPrabu i have three tables mentioned below bankbank_id bank_name------- ---------1 sbi2 icici3 hdfc4 rbs5 hsbclocationlocation_id location_city----------- ------------1 NY2 Colorado3 gzb4 london5 bankokbank_locationbank_id location_id---- ------1 11 21 31 41 53 23 44 15 25 5Find 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 DELHIHDFC NOIDAHSBC NOIDASBI DELHISBI NOIDAHERE SBI having both locations,but i need fetch bank_names which are not in both locations.help me to solvethanks in advance 解决方案 This Query will give your answerDECLARE @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)TWHERE T.Row>1)Hi ,Check the following CodeDECLARE @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_cityFROM @BankDtksWHERE 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 这篇关于获取不在两个位置的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!