mysql exist运用

-- 后台的
select a.province_id,b.name from tbl_withhold_province_station a,tbl_province b where a.province_id = b.id;




-- 已签约的省份列表
select distinct(a.province_id),b.province_name from tbl_vehicle_withhold_agreement a,tbl_withhold_province_station b where a.province_id = b.province_id 
and vehicle_lic = '京A12345';


-- 已签约的方案二
select province_id,`province_name` from tbl_withhold_province_station a where exists (select province_id from tbl_vehicle_withhold_agreement
b where a.province_id = b.province_id)




-- 是否可以添加代扣范围
-- 方案一
select count(distinct(a.province_id)) as sign_pro_count from tbl_vehicle_withhold_agreement a,tbl_withhold_province_station b where a.province_id = b.province_id 
and vehicle_lic = '京A12345';


select count(distinct(province_id)) as all_pro_count from tbl_withhold_province_station;


-- 方案二
select province_id from tbl_withhold_province_station where province_id in (select GROUP_CONCAT(distinct(province_id)) from tbl_vehicle_withhold_agreement
);


select * from tbl_withhold_province_station a where exists (select province_id from tbl_vehicle_withhold_agreement
b where a.province_id = b.province_id);


select province_id,province_name from tbl_withhold_province_station a where  not exists (select province_id from tbl_vehicle_withhold_agreement
b where a.province_id = b.province_id and vehicle_lic = '京A12347');