smallName完整版

SELECT
	a.sj completeTime,
	ad.id,
CASE
	
	WHEN ad.dScopeId IS NULL THEN
	'否' 
	WHEN ad.dScopeId = '' THEN
	'否' ELSE '是' 
	END dScopeId,
	ad.khbh,
	( SELECT area_name FROM T_area WHERE id = ad.sspq ) sspq,
	kd.smallName smallName,
	( SELECT statusname FROM flow_node WHERE nodecode = ad.nodecode AND nodecode NOT LIKE 'k00%' ) statusname,
	ad.typeName,
	ad.secondTypeName,
	ad.itemName,
	CONVERT ( FLOAT, Round( at1.decreaseScopeTop, 3 ) ) decreaseScopeTop,
	dfc.remark contract,
	REPLACE ( ad.requireName, '\r\n', '' ) requireName,
	ad.repairRequired,
	REPLACE ( ad.decreaseScopeStandard, '\r\n', '' ) decreaseScopeStandard,
	CONVERT ( FLOAT, Round( ad.num, 3 ) ) num,
	ad.unit,
	CONVERT ( FLOAT, Round( ad.decreaseScode, 3 ) ) decreaseScode,
	( SELECT displayname FROM userinfo WHERE username = ad.addPerson ) addDisplayname,
	( SELECT officename FROM officeinfo WHERE officeid = ad.addOffice ) addOfficeName,
	ad.addTime,
	ad.nodeUpdateTime,
	ad.lon,
	ad.lat,
	ad.position,
	ad.remark,
	ad.assessType,
	de.kfbh,
	CONVERT ( FLOAT, Round( de.decreaseScode, 3 ) ) deScore,
	de.addTime deAddTime 
FROM
	assessData ad
	LEFT JOIN assessStandard at1 ON at1.standardType = ad.assessType 
	AND at1.CODE = ad.itemCode
	LEFT JOIN DFCCODE dfc ON ad.contract_code = dfc.type_code
	LEFT JOIN decreaseScopeItem de ON ad.dScopeId = de.id
	LEFT JOIN (
	SELECT
		pid,
		max( log.czsj ) sj 
	FROM
		yhAssessLog log
		LEFT JOIN assessData ad ON ad.id = log.pid 
	WHERE
		ad.ISDELETE = '0' 
		AND pid = ad.id 
		AND czzt = '整改' 
	GROUP BY
		log.pid 
	) a ON a.pid = ad.id
	LEFT JOIN assessComplete ac ON ac.pid = a.pid 
	AND ac.addTime = a.sj
	LEFT JOIN khDamInfo kd ON ac.id = kd.pid 
WHERE
	ad.isDelete = '0' 
	AND ad.addOffice LIKE '000102%' 
	AND ad.addTime >= '2022-04-01 19:38:26' 
	AND ad.addTime <= '2022-05-09 19:38:31' 
	AND ad.typeCode = 'AS01' 
	AND ad.secondTypeCode = 'AS0101' 
ORDER BY
ad.nodeUpdateTime DESC

核心代码

LEFT JOIN (
	SELECT
		pid,
		max( log.czsj ) sj 
	FROM
		yhAssessLog log
		LEFT JOIN assessData ad ON ad.id = log.pid 
	WHERE
		ad.ISDELETE = '0' 
		AND pid = ad.id 
		AND czzt = '整改' 
	GROUP BY
		log.pid 
	) a ON a.pid = ad.id
	LEFT JOIN assessComplete ac ON ac.pid = a.pid 
	AND ac.addTime = a.sj
	LEFT JOIN khDamInfo kd ON ac.id = kd.pid