case when 的用法

SELECT
	(
		CASE
		WHEN (SUBSTR(xxjgdzdm, 10, 1) = '0')
		OR (SUBSTR(xxjgdzdm, 10, 1) = '1')
		OR (SUBSTR(xxjgdzdm, 10, 1) = '4') THEN
			'0'
		WHEN (SUBSTR(xxjgdzdm, 10, 1) = '2')
		OR (SUBSTR(xxjgdzdm, 10, 1) = '3')
		OR (SUBSTR(xxjgdzdm, 10, 1) = '5') THEN
			'1'
		END
	) AS sfxcxx,
	COUNT (T .SCHOOL_ID) AS school,
	(/~[ classCount ]~/) AS CLASS,
	(/~[ studentCount ]~/) AS student
FROM
	(
		SELECT
			*
		FROM
			BASE_SCHOOL /~[ DATE ]~/
		UNION
			SELECT
				*
			FROM
				BASE_SCHOOL_FSB /~[ DATE ]~/
	) T
WHERE
	1 = 1 /~
AND T .SSXD IN [ SSXD ]~/ /~
AND T .PROVINCE_ID = { provinceId }~/ /~
AND T .city_id = { cityId }~/ /~
AND T .COUNTY_ID = { countyId }~/ /~[ otherP ]~/
GROUP BY
	(
		CASE
		WHEN (SUBSTR(xxjgdzdm, 10, 1) = '0')
		OR (SUBSTR(xxjgdzdm, 10, 1) = '1')
		OR (SUBSTR(xxjgdzdm, 10, 1) = '4') THEN
			'0'
		WHEN (SUBSTR(xxjgdzdm, 10, 1) = '2')
		OR (SUBSTR(xxjgdzdm, 10, 1) = '3')
		OR (SUBSTR(xxjgdzdm, 10, 1) = '5') THEN
			'1'
		END
	)

SELECT
 count(DISTINCT t.SCHOOL_ID) as SCHOOL_NUM,
	t.CITY_ID as areaid,
	COUNT (*) AS DANTI_NUM,
	COUNT (
		CASE
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_xs%'
			AND NVL (TO_NUMBER(T .ZJ_HJ), 0) = NVL (
				TO_NUMBER (T .FINISH_ZJ_HJ),
				0
			)
			AND NVL (TO_NUMBER(T .XS_01_XSMJ), 0) = NVL (
				TO_NUMBER (T .FINISH_GM_HJ),
				0
			)
		) THEN
			'0'
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_ydcd%'
			AND NVL (TO_NUMBER(T .ZJ_HJ), 0) = NVL (
				TO_NUMBER (T .FINISH_ZJ_HJ),
				0
			)
			AND NVL (
				TO_NUMBER (T .YDCD_01_SWYDCDMJ),
				0
			) = NVL (
				TO_NUMBER (T .FINISH_GM_HJ),
				0
			)
		) THEN
			'1'
		END
	) AS XIADA_NUM,
	COUNT (
		CASE
		WHEN (
			NVL (
				TO_NUMBER (T .FINISH_ZJ_HJ),
				0
			) = 0
			AND NVL (
				TO_NUMBER (T .FINISH_GM_HJ),
				0
			) = 0
		) THEN
			'0'
		END
	) AS XIADA_NONE_NUM,
	SUM (
		CASE
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_xs%'
		) THEN
			T .ZJ_HJ
		END
	) AS XS_MONEY,
	COUNT (
		CASE
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_xs%'
			AND NVL (TO_NUMBER(T .ZJ_HJ), 0) = NVL (
				TO_NUMBER (T .FINISH_ZJ_HJ),
				0
			)
		) THEN
			'0'
		END
	) AS XS_MONEY_XIADA_NUM,
	COUNT (
		CASE
		WHEN (
			NVL (
				TO_NUMBER (T .FINISH_ZJ_HJ),
				0
			) = 0
			AND T .ITEM_TYPE LIKE 'item_type_xs%'
		) THEN
			'0'
		END
	) AS XS_MONEY_XIADA_NONE_NUM,
	SUM (
		CASE
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_xs%'
		) THEN
			T .XS_01_XSMJ
		END
	) AS XS_MJ,
	COUNT (
		CASE
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_xs%'
			AND NVL (TO_NUMBER(T .XS_01_XSMJ), 0) = NVL (
				TO_NUMBER (T .FINISH_GM_HJ),
				0
			)
		) THEN
			'0'
		END
	) AS XS_MJ_XIADA_NUM,
	COUNT (
		CASE
		WHEN (
			NVL (TO_NUMBER(T .FINISH_GM_HJ), 0) = 0
			AND T .ITEM_TYPE LIKE 'item_type_xs%'
		) THEN
			'0'
		END
	) AS XS_MJ_XIADA_NONE_NUM,
	
	SUM (
		CASE
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_ydcd%'
		) THEN
			T .ZJ_HJ
		END
	) AS YDCD_MONEY,
	COUNT (
		CASE
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_ydcd%'
			AND NVL (TO_NUMBER(T .ZJ_HJ), 0) = NVL (
				TO_NUMBER (T .FINISH_ZJ_HJ),
				0
			)
		) THEN
			'0'
		END
	) AS YDCD_MONEY_XIADA_NUM,
	COUNT (
		CASE
		WHEN (
			NVL (
				TO_NUMBER (T .FINISH_ZJ_HJ),
				0
			) = 0
			AND T .ITEM_TYPE LIKE 'item_type_ydcd%'
		) THEN
			'0'
		END
	) AS YDCD_MONEY_XIADA_NONE_NUM,
	SUM (
		CASE
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_ydcd%'
		) THEN
			T .YDCD_01_SWYDCDMJ
		END
	) AS YDCD_MJ,
	COUNT (
		CASE
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_ydcd%'
			AND NVL (TO_NUMBER(T .YDCD_01_SWYDCDMJ), 0) = NVL (
				TO_NUMBER (T .FINISH_GM_HJ),
				0
			)
		) THEN
			'0'
		END
	) AS YDCD_MJ_XIADA_NUM,
	COUNT (
		CASE
		WHEN (
			NVL (TO_NUMBER(T .FINISH_GM_HJ), 0) = 0
			AND T .ITEM_TYPE LIKE 'item_type_ydcd%'
		) THEN
			'0'
		END
	) AS YDCD_MJ_XIADA_NONE_NUM,
	
	SUM (
		CASE
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_fsss%'
		) THEN
			T .ZJ_HJ
		END
	) AS FSSS_MONEY,
	COUNT (
		CASE
		WHEN (
			T .ITEM_TYPE LIKE 'item_type_fsss%'
			AND NVL (TO_NUMBER(T .ZJ_HJ), 0) = NVL (
				TO_NUMBER (T .FINISH_ZJ_HJ),
				0
			)
		) THEN
			'0'
		END
	) AS FSSS_MONEY_XIADA_NUM,
	COUNT (
		CASE
		WHEN (
			NVL (
				TO_NUMBER (T .FINISH_ZJ_HJ),
				0
			) = 0
			AND T .ITEM_TYPE LIKE 'item_type_fsss%'
		) THEN
			'0'
		END

) AS FSSS_MONEY_XIADA_NONE_NUM
FROM
	BASE_IMPORT_GUIHUA T
WHERE
	1 = 1

GROUP BY
	T .city_id