case语句的使用

1.结合update的set使用：

UPDATE group_relation_station 
SET score_reduction=
(CASE	WHEN score_reduction < 100 THEN (score_reduction + 0.1) ELSE  100  END ),
 assessmentdate = now() 
 WHERE stationcode in 
 (select code from sensor_monitoring_stations s where s.id = new.station_id);

2.select中使用

BEGIN
              UPDATE station_group_score_result  SET score_result= 100 WHERE score_result is null;
               UPDATE station_group_score_result r,
 (
	SELECT
		orgid,
		CASE
	WHEN (100 - SUM(score_reduction)) < 0 THEN
		0
	ELSE
		(100 - SUM(score_reduction))
	END AS score_result_new
	FROM
		station_group
	GROUP BY
		orgid
) sc
SET r.score_result = sc.score_result_new
WHERE
	r.orgid = sc.orgid;
END