COALESCE 函数 

 1 SET NOCOUNT ON;
 2 GO
 3 USE tempdb;
 4 IF OBJECT_ID('dbo.wages') IS NOT NULL
 5     DROP TABLE wages;
 6 GO
 7 CREATE TABLE dbo.wages
 8 (
 9     emp_id        tinyint   identity,
10     hourly_wage   decimal   NULL,
11     salary        decimal   NULL,
12     commission    decimal   NULL,
13     num_sales     tinyint   NULL
14 );
15 GO
16 INSERT dbo.wages (hourly_wage, salary, commission, num_sales)
17 VALUES
18     (10.00, NULL, NULL, NULL),
19     (20.00, NULL, NULL, NULL),
20     (30.00, NULL, NULL, NULL),
21     (40.00, NULL, NULL, NULL),
22     (NULL, 10000.00, NULL, NULL),
23     (NULL, 20000.00, NULL, NULL),
24     (NULL, 30000.00, NULL, NULL),
25     (NULL, 40000.00, NULL, NULL),
26     (NULL, NULL, 15000, 3),
27     (NULL, NULL, 25000, 2),
28     (NULL, NULL, 20000, 6),
29     (NULL, NULL, 14000, 4);
30 GO
31 SET NOCOUNT OFF;
32 GO
33 SELECT CAST(COALESCE(hourly_wage * 40 * 52, salary,commission * num_sales) AS money) AS 'Total Salary' 
34 FROM dbo.wages
35 ORDER BY 'Total Salary';
36 GO
37 --COALESCE(Class, Color, ProductNumber) AS FirstNotNull 
38 
39 
40 select coalesce(hourly_wage, salary, commission, num_sales) as ds from dbo.wages
41 select coalesce(hourly_wage, salary, commission) as ds from dbo.wages
42 select * from dbo.wages
43 select coalesce(hourly_wage,num_sales) as ds from dbo.wages
44 select * from dbo.wages

 

posted @ 2017-04-18 20:31  Devoloper_Jack  阅读(245)  评论(0)    收藏  举报