如何使用连接编写确切的查询。请指导我

40 136 D NULL ------------------------- -------------------------------------------------- ------------------ - 秒表 从距离选择* - A的地址是10，距离枢纽136公里50公里 - 其中，地址ID为B距离枢纽136公里50公里 address_id hub_id位置距离 10 136 A 50 20 136 B 75 30 136 C 85 40 136 D 95 =================我想要的所需输出======== ==================================== - 现在我必须在主表（第一个表）中选择HUB_ID 136. - 所需输出为 ADDRESS_ID HUB_ID位置距离 10 136 A 50 20 136 B 75 30 136 C 85 40 136 D 95 - 如果我尝试选择集线器为20距离应该为空，因为距离不在第二个表（距离表）中的hub_id 20 ADDRESS_ID HUB_ID位置距离 10 136 A NULL 20 136 B NULL 30 136 C NULL 40 136 D NULL ======================== ================================================== ================== - 我试图用这种方式写作 - 这是工作 选择m.address_id，m.hub_id，m.location，d.distance from main m 加入距离d on m.address_id = d.address_id 其中m.hub_id = 136 address_id hub_id位置距离 10 136 A 50 20 136 B 75 30 136 C 85 40 136 D 95 - 但如果我选择20 - 这不是所需的输出，因为距离应该为空 选择m.address_id ，m.hub_id，m.location，d.distance from main m 加入距离d on m.address_id = d.address_id 其中m.hub_id = 20 address_id hub_id位置距离 10 20 A 50 20 20 B 20 20 C 85 20 20 D 95 - 我正在尝试这个...但没有结果 选择m.address_id，m.hub_id，m.location，d.distance $ m $ b来自主m 左加入距离d on m.address_id = d.address_id 其中m.hub_id = 20 和d.hub_id = 20 请帮忙这是紧急... 解决方案 当您加入表时，请确保包含需要在ON子句中链接的所有列。你的WHERE子句应该只包含主表的过滤器： 选择 m.address_id，m.hub_id，m.location，d.distance 来自 main m left join 距离d on m.address_id = d.address_id AND m.hub_id = d.hub_id AND m.location = d.location where m.hub_id = 20 请在撰写问题时使用标签 ; cte（address_Id，hub_id，location，distance） as （ 选择 m.address_id ，m.hub_id ，m.location ，d.distance 来自 main m left join 距离 d on m.address_id = d.address_id 和 m.hub_id = d.hub_id 和 m.location = d.location ） 选择 * 来自 cte 其中 hub_id = 20 --Scenario--first tableselect * from main--here the addressid of A IS 10...addressid of B IS 20....addressid of C IS 30--two hub id is there in the table 20 and 136address_id hub_id location distance1020ANULL2020BNULL3020CNULL4020DNULL10136ANULL20136BNULL30136CNULL40136DNULL-----------------------------------------------------------------------------------------------second tableselect * from distance--HERE THE ADDRESSID OF A That is 10 is 50 km from hub 136--HERE the addressid OF B That is 20 is 50 km from hub 136address_id hub_id location distance10136A5020136B7530136C8540136D95=================Desired output which i want ============================================--now i have to select in main table(first table) with HUB_ID 136 .--desired output asADDRESS_ID HUB_ID LOCATION DISTANCE10 136 A 5020 136 B 7530 136 C 8540 136 D 95--and if i try to select the hub as 20 distance should be null as distance is not there for hub_id 20 in second table(distance table)ADDRESS_ID HUB_ID LOCATION DISTANCE10 136 A NULL20 136 B NULL30 136 C NULL40 136 D NULL============================================================================================-- i was trying to write in this way--ok this is workingselect m.address_id,m.hub_id,m.location,d.distancefrom main m join distance d on m.address_id=d.address_idwhere m.hub_id=136address_id hub_id location distance10 136 A 5020 136 B 7530 136 C 8540 136 D 95-- but if i select for 20--this is not desired output as distance should be nullselect m.address_id,m.hub_id,m.location,d.distancefrom main m join distance d on m.address_id=d.address_idwhere m.hub_id=20address_id hub_id location distance10 20 A 5020 20 B 7530 20 C 8540 20 D 95-- i was trying this ...but no resultselect m.address_id,m.hub_id,m.location,d.distancefrom main m left join distance d on m.address_id=d.address_idwhere m.hub_id=20and d.hub_id=20 please help this is urgent... 解决方案 When you join the tables make sure you include all columns that need to be linked in your ON clause. Your WHERE clause should contain only the filter for the main table:select m.address_id,m.hub_id,m.location,d.distancefrom main mleft join distance d on m.address_id=d.address_id AND m.hub_id=d.hub_id AND m.location = d.locationwhere m.hub_id=20And please, use the tags when you writing the questions;with cte(address_Id,hub_id,location,distance)as(select m.address_id , m.hub_id , m.location , d.distance from main as m left join Distance as d on m.address_id = d.address_id and m.hub_id = d.hub_id and m.location = d.location)select * from ctewhere hub_id = 20 这篇关于如何使用连接编写确切的查询。请指导我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！