select tpi.id, sd.dept_id, sd.dept_name, tpi.year, tpi.project_name, tpi.construction_company, tpc.company_name as constructionCompanyName, (select sdd.dict_label from sys_dict_data sdd where sdd.dict_type = 'contract_segment' and sdd.dict_value = tpi.contract_segment) contractSegmentName, tpi.is_section from t_project_info tpi left join t_participating_company tpc on tpi.construction_company = tpc.id left join sys_dept sd on tpi.dept_id = sd.dept_id where tpi.del_flag = 0 and tpi.year = Date_format(now(),'%Y') and tpi.is_section = 0 and tpi.id not in(select group_concat(tci.project_id) from t_contract_info tci where tci.del_flag = 0 and tci.project_id != '');子查询语句和手动填的一致,结果却不一样
时间: 2024-04-20 16:27:28 浏览: 161
如果子查询语句和手动填的一致,但结果却不一样,可能是因为使用了 GROUP_CONCAT 函数来返回子查询结果中多个项目ID的字符串,而 NOT IN 语句中只能比较单个值,不能直接比较字符串。因此,你需要使用 INSTR 函数来判断子查询结果中是否包含指定的项目ID。例如:
SELECT tpi.id, sd.dept_id, sd.dept_name, tpi.year, tpi.project_name, tpi.construction_company, tpc.company_name as constructionCompanyName, (SELECT sdd.dict_label FROM sys_dict_data sdd WHERE sdd.dict_type = 'contract_segment' AND sdd.dict_value = tpi.contract_segment) contractSegmentName, tpi.is_section
FROM t_project_info tpi
LEFT JOIN t_participating_company tpc ON tpi.construction_company = tpc.id
LEFT JOIN sys_dept sd ON tpi.dept_id = sd.dept_id
WHERE tpi.del_flag = 0 AND tpi.year = DATE_FORMAT(NOW(), '%Y') AND tpi.is_section = 0 AND NOT EXISTS (
SELECT 1
FROM t_contract_info tci
WHERE tci.del_flag = 0 AND tci.project_id != '' AND INSTR(CONCAT(',', tci.project_id, ','), CONCAT(',', tpi.id, ','))
)
这里使用了 CONCAT 函数将子查询结果中多个项目ID拼接成一个字符串,然后使用 INSTR 函数来判断是否包含指定的项目ID。注意,这里在子查询中也使用了 NOT IN,但是为了避免子查询结果中包含 NULL 值导致 NOT IN 失效,使用了 NOT EXISTS 来避免这个问题。
阅读全文