sql语句中使用if改变列的值和sql语句根据月份分组并判断高级条件的语法

1.sql语句中使用if改变列的值和sql语句根据月份分组并判断高级条件的语法

(1)使用if改变列的值的sql语句

SELECT
	sum(( ( IF ( plan_invested_amount_centre IS NULL, 0, plan_invested_amount_centre ) ) * ( IF ( invest_money_unit_centre = 2, 6.992, 1 ) ) ) + ( ( IF ( plan_invested_amount_outer IS NULL, 0, plan_invested_amount_outer ) ) * ( IF ( invest_money_unit_outer = 2, 6.992, 1 ) ) ) )
	 AS money 
FROM
	project_info 
WHERE
	is_delete = 0 
	AND STATUS = 0 
	AND is_recycle = 0 
	AND ( ( step1 = 3 AND step2 >= 2 ) OR step1 = 4 OR step1 = 5 )
	and negotiation_time >= '2020-01-13 18:27:46'
            and '2020-09-13 18:27:46' >= negotiation_time

大致解释：if括号里面的数据，第一个参数是判断语句，第二个是判断语句成立时列要变成的值，第三个参数是判断语句不成立时列要变成的值

(2)根据月份分组并判断高级条件的sql语句

SELECT
	DATE_FORMAT( negotiation_time, '%Y-%m' ) AS MONTH,
	sum(
	( ( IF ( plan_invested_amount_centre IS NULL, 0, plan_invested_amount_centre ) ) * ( IF ( invest_money_unit_centre = 2, 6.992, 1 ) ) ) + ( ( IF ( plan_invested_amount_outer IS NULL, 0, plan_invested_amount_outer ) ) * ( IF ( invest_money_unit_outer = 2, 6.992, 1 ) ) ) 
	) AS money 
FROM
	project_info 
WHERE
	is_delete = 0 
	AND STATUS = 0 
	AND is_recycle = 0 
	AND ( ( step1 = 3 AND step2 >= 2 ) OR step1 = 4 OR step1 = 5 ) 
	AND negotiation_time IS NOT NULL 
GROUP BY
	DATE_FORMAT( negotiation_time, '%Y-%m' );

大致解释：根据月份分组，DATE_FORMAT( date, '%Y-%m' );就是把date的时间变成2020-01这样的格式