SQL SERVER coalesce的用法解决办法_Sql Server

SQL SERVER coalesce的用法
SQL SERVER coalesce的用法

------解决方案--------------------

SQL code

--返回其参数中的第一个非空表达式。--例:declare @i int,@j int,@n int,@k intset @n=5set @k=6select COALESCE(@i,@j,@n,@k)/*-----------5(1 行受影响)*/
------解决方案--------------------
SQL code等同:CASE   WHEN (expression1 IS NOT NULL) THEN expression1   ...   WHEN (expressionN IS NOT NULL) THEN expressionN   ELSE NULLENDSET NOCOUNT ON;GOUSE master;IF EXISTS (SELECT name FROM sys.tables      WHERE name = 'wages')   DROP TABLE wages;GOCREATE TABLE wages(   emp_id      tinyint    identity,   hourly_wage   decimal   NULL,   salary      decimal    NULL,   commission   decimal   NULL,   num_sales   tinyint   NULL);GOINSERT wages VALUES(10.00, NULL, NULL, NULL);INSERT wages VALUES(20.00, NULL, NULL, NULL);INSERT wages VALUES(30.00, NULL, NULL, NULL);INSERT wages VALUES(40.00, NULL, NULL, NULL);INSERT wages VALUES(NULL, 10000.00, NULL, NULL);INSERT wages VALUES(NULL, 20000.00, NULL, NULL);INSERT wages VALUES(NULL, 30000.00, NULL, NULL);INSERT wages VALUES(NULL, 40000.00, NULL, NULL);INSERT wages VALUES(NULL, NULL, 15000, 3);INSERT wages VALUES(NULL, NULL, 25000, 2);INSERT wages VALUES(NULL, NULL, 20000, 6);INSERT wages VALUES(NULL, NULL, 14000, 4);GOSET NOCOUNT OFF;GOSELECT CAST(COALESCE(hourly_wage * 40 * 52,    salary,    commission * num_sales) AS money) AS 'Total Salary' FROM wages;GO