<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yeejoin.amos.boot.biz.common.dao.mapper.DataDictionaryMapper">
<select id="getDataDictionaryListAndCount" resultType="com.yeejoin.amos.boot.biz.common.entity.DataDictionary">

SELECT
	cdd.*, count(cfe.sequence_nbr) AS count
FROM
	cb_data_dictionary cdd
LEFT JOIN (
	SELECT
		*
	FROM
		cb_fire_experts
	WHERE
		is_delete = 0
) cfe ON cdd.`code` = cfe.expert_code
WHERE
	cdd.type = 'ZJLY'
AND cdd.is_delete = 0
GROUP BY
	cdd. CODE ORDER BY sort_num 


</select>




<select id ="getNoInLinkUnit" resultType="com.yeejoin.amos.boot.biz.common.entity.DataDictionary">
SELECT
CONCAT(cbb.`name`,' (',elink.count,')') as `name`,
	cbb.*,
	elink.count as count
FROM
	cb_data_dictionary cbb
LEFT JOIN (
	SELECT
		emergency_linkage_unit_code,
		COUNT(1) count
	FROM
		cb_linkage_unit
	WHERE is_delete = 0
	GROUP BY
		emergency_linkage_unit_code
) elink ON elink.emergency_linkage_unit_code = cbb.`code`
WHERE
	cbb.type = 'YJLDDW' and cbb.is_delete = 0
AND elink.count IS NOT NULL
</select>

	<select id ="getByCode" resultType="com.yeejoin.amos.boot.biz.common.entity.DataDictionary">
		SELECT
			*
		FROM
			cb_data_dictionary cbb

		WHERE
			cbb.code = #{code} and cbb.is_delete = 0 and cbb.type = #{type}
	</select>
</mapper>
