Case when应用
// 如果column_4 = 'IT_PROG' 那么 输出 1.10 * column_3
// 如果column_4 = 'ST_CLERK' 那么 输出 1.15 * column_4
// 否则 输出 column_3 使用别名 "REVISED_SALARY"
SELECT column_1,column_2,column_3 CASE column_4 WHEN 'IT_PROG' THEN 1.10*column_3
WHEN 'ST_CLERK' THEN 1.15*column_3
WHEN 'SA_REP' THEN 1.20*column_3
ELSE column_3 END "REVISED_SALARY"
FROM table_name;
WHEN 'ST_CLERK' THEN 1.15*column_3
WHEN 'SA_REP' THEN 1.20*column_3
ELSE column_3 END "REVISED_SALARY"
FROM table_name;
//
SELECT column_1,column_2, SUM(CASE WHEN column_3 = '100' THEN column_4 ELSE 0 END) AS cost_100
FROM table_name
GROUP BY column_1,column_2;
FROM table_name
GROUP BY column_1,column_2;
// 如果 f_this_price = 0 或 f_last_price = 0 则结果为 0 ,如果都不为 0 时, 取两列差的合计
SELECT f_wzbm,
CASE WHEN SUM(f_this_price) = 0 OR SUM(f_last_price) = 0
THEN 0
ELSE SUM(f_this_price - f_last_price)
END f_markup_price
FROM table_nameGROUP BY f_wzbm;
CASE WHEN SUM(f_this_price) = 0 OR SUM(f_last_price) = 0
THEN 0
ELSE SUM(f_this_price - f_last_price)
END f_markup_price
FROM table_nameGROUP BY f_wzbm;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/1724534/viewspace-1057558/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/1724534/viewspace-1057558/