SQL Server/T-SQL/Select Query/ISNULL — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10:20, 26 мая 2010
Содержание
Dealing with NULL Data - the ISNULL Function
1> -- Dealing with NULL Data - the ISNULL Function
2>
3>
4> select ISNULL(NULL, 5)
5>
6> GO
-----------
5
(1 rows affected)
1>
ISNULL(5, 15)
1> select ISNULL(5, 15)
2> GO
-----------
5
(1 rows affected)
1>
ISNULL allows a display of the specified value as substitution for NULL
1>
2> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
3> project_no CHAR(4) NOT NULL,
4> job CHAR (15) NULL,
5> enter_date DATETIME NULL)
6> GO
1> insert into works_on values (1, "p1", "analyst", "1997.10.1")
2> insert into works_on values (1, "p3", "manager", "1999.1.1")
3> insert into works_on values (2, "p2", "clerk", "1998.2.15")
4> insert into works_on values (2, "p2", NULL, "1998.6.1")
5> insert into works_on values (3, "p2", NULL, "1997.12.15")
6> insert into works_on values (4, "p3", "analyst", "1998.10.15")
7> insert into works_on values (5, "p1", "manager", "1998.4.15")
8> insert into works_on values (6, "p1", NULL, "1998.8.1")
9> insert into works_on values (7, "p2", "clerk", "1999.2.1")
10> insert into works_on values (8, "p3", "clerk", "1997.11.15")
11> insert into works_on values (7, "p1", "clerk", "1998.1.4")
12> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> -- ISNULL allows a display of the specified value as substitution for NULL
3>
4> SELECT emp_no, ISNULL(job, "Job unknown") task FROM works_on WHERE project_no = "p1"
5> GO
emp_no task
----------- ---------------
1 analyst
5 manager
6 Job unknown
7 clerk
(4 rows affected)
1>
2> drop table works_on
3> GO
1>
ISNULL function demo
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, NULL , 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, NULL , 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 NULL 26020 2001-07-08 00:00:00.000 Vancouver N
9 NULL 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> SELECT TOP 3 Name, ISNULL(Name, "No Name") FROM Employee
3> GO
Name
---------- ----------
Jason Jason
Robert Robert
Celia Celia
(3 rows affected)
1>
2>
3>
4>
5> drop table employee
6> GO
1>
ISNULL with subquery
11> CREATE TABLE titles(
12> title_id varchar(20),
13> title varchar(80) NOT NULL,
14> type char(12) NOT NULL,
15> pub_id char(4) NULL,
16> price money NULL,
17> advance money NULL,
18> royalty int NULL,
19> ytd_sales int NULL,
20> notes varchar(200) NULL,
21> pubdate datetime NOT NULL
22> )
23> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3>
4> SELECT title, pub_id, ISNULL(price, (SELECT MIN(price)
5> FROM titles)) FROM titles
6> GO
title pub_id
-------------------------------------------------------------------------------- ------ ---------------------
Secrets 1389 20.0000
The 1389 19.9900
Emotional 0736 7.9900
Prolonged 0736 19.9900
With 1389 11.9500
Valley 0877 19.9900
Any? 0877 14.9900
Fifty 0877 11.9500
(8 rows affected)
1>
2>
3> drop table titles;
4> GO
Using ISNULL in select statement
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> select ISNULL(ID, 0) from Employee where ID = 3
3>
4> drop table employee
5> GO
-----------
3
(1 rows affected)
1>