MySQL 如何去除结果中不存在的数据

求助问答
1 
SELECT
	( SELECT platformcode FROM cfc_nocarcarrierplatforminfo WHERE isdeleteflag = 0 ) AS platformcode,
	a.driver_id AS carrierid,
	( CASE WHEN a.type = 1 THEN 2 ELSE 1 END ) AS carriertype,  
	a.driver_name AS carriername,
	a.identification_number AS unifiedsocialcreditldentifier,    //去除空数据
	b.road_transport_business_license_no AS permitnumber,  //去除空数据
	a.area_id AS countrysubdivisioncode, //去除空数据 
	a.driver_name AS contactname,
	a.create_time AS registrationdatetime,
	a.phone AS contactmobiletelephonenumber,
	a.blacklist_status AS isblaclklist,
	a.is_delete AS isdeleteflag,
	c.id,
	a.is_report_carrier_message AS report_status,
	a.report_time AS report_time,
	a.report_abnormal_reason AS err_msg 
FROM
	tms_driver a
	LEFT JOIN tms_vehicle b ON a.vehicle_type_id = b.id
	LEFT JOIN cfc_carrierinfo c ON a.driver_id = c.carrierid 
WHERE
	a.is_delete = 0

大佬们，995

2

没懂你这“除结果中不存在的数据” 是啥意思。

如果是关联查询，如果关联数据不存在。就不查询的话，那么。
1，用INNER JOIN添加WHERE 条件
2，子查询，添加 EISTS条件

3 
SELECT
	( SELECT platformcode FROM cfc_nocarcarrierplatforminfo WHERE isdeleteflag = 0 ) AS platformcode,
	a.driver_id AS carrierid,
	( CASE WHEN a.type = 1 THEN 2 ELSE 1 END ) AS carriertype,
	a.driver_name AS carriername,
	a.identification_number AS unifiedsocialcreditldentifier  ,
	b.road_transport_business_license_no AS permitnumber,
	a.area_id AS countrysubdivisioncode,
	a.driver_name AS contactname,
	a.create_time AS registrationdatetime,
	a.phone AS contactmobiletelephonenumber,
	a.blacklist_status AS isblaclklist,
	a.is_delete AS isdeleteflag,
	c.id,
	a.is_report_carrier_message AS report_status,
	a.report_time AS report_time,
	a.report_abnormal_reason AS err_msg 
FROM
	tms_driver a
	LEFT JOIN tms_vehicle b ON a.vehicle_type_id = b.id
	LEFT JOIN cfc_carrierinfo c ON a.driver_id = c.carrierid 
WHERE
	a.is_delete = 0  and 
 unifiedsocialcreditldentifier   is not null

unifiedsocialcreditldentifier is not null

4

好像还是不行，不知道问题出现在哪里
下面是用了 inner join 的

SELECT
	( SELECT platformcode FROM cfc_nocarcarrierplatforminfo WHERE isdeleteflag = 0 ) AS platformcode,
	a.driver_id AS carrierid,
	( CASE WHEN a.type = 1 THEN 2 ELSE 1 END ) AS carriertype,
	a.driver_name AS carriername,
	a.identification_number AS unifiedsocialcreditldentifier,
	b.road_transport_business_license_no AS permitnumber,
	a.area_id AS countrysubdivisioncode,
	a.driver_name AS contactname,
	a.create_time AS registrationdatetime,
	a.phone AS contactmobiletelephonenumber,
	a.blacklist_status AS isblaclklist,
	a.is_delete AS isdeleteflag,
	c.id,
	a.is_report_carrier_message AS report_status,
	a.report_time AS report_time,
	a.report_abnormal_reason AS err_msg 
FROM
	tms_driver a
	inner JOIN tms_vehicle b ON a.vehicle_type_id = b.id
	inner JOIN cfc_carrierinfo c ON a.driver_id = c.carrierid 
WHERE
	a.is_delete = 0 
	AND countrysubdivisioncode IS not NULL    // 国家区划代码不能为空

查出来数据 694 条，系统里已经上报的数据也是这么多

但我删除了最后 IS NOT NULL 判断后

我把数据库导出到EXCLE里面

国家区划代码存在的记录却有 6681 条

1628816396(1)
1628816396(1)1920×1010 201 KB

不知道大佬是否明白我的意思

求大佬指点迷津

5

？？那你是要为空的还是不为空的 ，如果要导出不为空的 为啥去掉 is not null

6

就是说我实际满足条件的数据是6681条

用inner join 只查出 694 条

7

1628818595(1)
1628818595(1)706×169 8.33 KB

你这意思是 删除了is not null 查询出来的是 正确的？？

8

是 6681 条记录有值，只查出 694条

9
  1. 你的目的是要查出来 6681条数据还是694
  2. countrysubdivisioncode IS not NULL 这个条件要不要满足
10

1、我想查出的是6681
2、countrysubdivisioncode IS not NULL 条件要满足

11

这个hon

1628822169(1)
1628822169(1)1285×790 170 KB

红色quan圈圈里面的是countrysubdivisioncode ？？ 如果是的话，你导出来的excel里面的总数也是把空的算进去的，但是满足条件is not null 是不为空的意思，那必然小于 6681

12

不是，这个表一共有2W多条数据，我需要查出满足条件的数据

其中一个就是 countrysubdivisioncode

EXCEL里 countrysubdivisioncode 有值的数据有 6681条

但我只查出 694条

13

这样的话你要告诉有些具体的信息 需要清楚你的基础业务信息 然后做查询 你可以弄几个模拟的表 然后代替你的实际场景的字段 再然后告诉你要查的条件

14

好的吧，我试试