SELECT
wac2.id as id
FROM
web_area_school wac2
WHERE
wac2.id NOT IN (
SELECT
was.id
FROM
web_base_school wbs,
(
SELECT
CASE (LEFT(wac.area_code, 4))
WHEN 1100 THEN
110
WHEN 1200 THEN
120
WHEN 3100 THEN
310
WHEN 5000 THEN
500
ELSE
LEFT (wac.area_code, 4)
END AS area_code,
wac.id
FROM
web_area_school wac
) was
WHERE
wbs.school_code LIKE CONCAT('%', was.area_code, '%') and wbs.del_flag=1 and wbs.type=1
AND wbs.school_status >= 0
GROUP BY
was.area_code
)
根据某个字段值去修改另一个字段
UPDATE web_base_school wbs,
(
SELECT
COUNT(wbs2.id) AS schoolNum,
wbs2.id
FROM
web_base_school wbs2
WHERE
wbs2.area_code = #{olderCityID}
AND wbs2.type = 1
AND wbs2.school_status >= 0
and wbs2.del_flag=1
) wbs3
SET wbs.school_status = CASE wbs3.schoolNum
WHEN 0 THEN
'-1'
ELSE
wbs.school_status
END
where wbs.area_code = #{olderCityID} and type=0