SQL Server/T-SQL/Select Query/Case
Содержание
- 1 A simple CASE takes an expression that equates to a Boolean result
- 2 CASE: return a specified value based on a set of business logic
- 3 Case when and else
- 4 Case when NULL
- 5 "Case when" statement in a procedure
- 6 Case When with else
- 7 Case when with Mod and ABS()
- 8 Select case statement
- 9 Use CASE in the UPDATE statement
A simple CASE takes an expression that equates to a Boolean result
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> --A simple CASE takes an expression that equates to a Boolean result
3>
4> SELECT TOP 10 ID, Position =
5> CASE ID % 10
6> WHEN 1 THEN "First"
7> WHEN 2 THEN "Second"
8> WHEN 3 THEN "Third"
9> WHEN 4 THEN "Fourth"
10> ELSE "Something Else"
11> END
12> FROM Employee
13>
14>
15> drop table employee
16> GO
ID Position
----------- --------------
1 First
2 Second
3 Third
4 Fourth
5 Something Else
6 Something Else
7 Something Else
8 Something Else
9 Something Else
(9 rows affected)
1>
CASE: return a specified value based on a set of business logic
11>
12> -- CASE: return a specified value based on a set of business logic
13>
14> DECLARE @Status Int
15> SET @Status = 1
16>
17> SELECT CASE @Status
18> WHEN 1 THEN "Active"
19> WHEN 2 THEN "Inactive"
20> WHEN 3 THEN "Pending"
21> END
22>
23> GO
--------
Active
(1 rows affected)
1>
Case when and else
1>
2> CREATE TABLE project (project_no CHAR(4) NOT NULL,
3> project_name CHAR(15) NOT NULL,
4> budget FLOAT NULL)
5>
6> insert into project values ("p1", "Search Engine", 120000.00)
7> insert into project values ("p2", "Programming", 95000.00)
8> insert into project values ("p3", "SQL", 186500.00)
9>
10> -- CASE Expressions
11>
12> SELECT project_name,
13> CASE
14> WHEN budget > 0 AND budget < 100000 THEN 1
15> WHEN budget >= 100000 AND budget < 200000 THEN 2
16> WHEN budget >= 200000 AND budget < 300000 THEN 3
17> ELSE 4
18> END budget_weight
19> FROM project
20> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
project_name budget_weight
--------------- -------------
Search Engine 2
Programming 1
SQL 2
(3 rows affected)
1>
2> drop table project
3> GO
1>
2>
Case when NULL
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 with a CASE expression:
3>
4> SELECT ID
5> , Name
6> , CASE ID
7> WHEN 1 THEN "Mountain"
8> WHEN 2 THEN "Road"
9> WHEN 3 THEN "Touring"
10> WHEN Null THEN "Something"
11> ELSE "No"
12> END As IDX
13> FROM Employee
14> GO
ID Name IDX
----------- ---------- ---------
1 Jason Mountain
2 Robert Road
3 Celia Touring
4 Linda No
5 David No
6 James No
7 Alison No
8 Chris No
9 Mary No
(9 rows affected)
1>
2> drop table employee
3> GO
1>
"Case when" statement in a procedure
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, regio
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, regio
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, regio
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, regio
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, regio
2> values (5, "David", 126, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, regio
2> values (6, "James", 7006, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, regio
2> values (7, "Alison",9062, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, regio
2> values (8, "Chris", 2000, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, regio
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 regio
----------- ---------- ----------- ----------------------- ---------- -----
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 126 1998-10-05 00:00:00.000 Vancouver W
6 James 7006 1999-09-06 00:00:00.000 Toronto N
7 Alison 9062 2000-08-07 00:00:00.000 New York W
8 Chris 2000 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> --Using the CASE Statement
3> drop procedure sp_demo
4> GO
1>
2> CREATE PROCEDURE sp_Demo
3> @StartDate DATETIME,
4> @ID INT = 1
5> WITH ENCRYPTION
6> AS
7> IF @StartDate > GETDATE()
8> RETURN 1
9> IF @ID < 0
10> RETURN 2
11>
12> SELECT "Result" =
13> CASE WHEN salary > 1000 THEN "> 1000"
14> WHEN salary = 2000 THEN "= 2000"
15> WHEN salary < 8000 THEN "< 8000"
16> END
17> FROM Employee
18> WHERE Id <> @ID
19> AND Start_Date <> @StartDate
20> GO
1>
2>
3> DECLARE @Start_Date_In smalldatetime
4> DECLARE @Id_In INT
5> SET @Start_Date_In = "24 March 2001"
6> SET @ID_In = 1
7> EXECUTE sp_Demo @Start_Date_In, @ID_In
8>
9>
10> drop table employee
11> GO
Result
------
> 1000
> 1000
> 1000
< 8000
> 1000
> 1000
> 1000
> 1000
(8 rows affected)
1>
Case When with else
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 ID
3> , Name
4> , SubCategory = CASE ID
5> WHEN 1 THEN "M"
6> WHEN 2 THEN "Road Bike"
7> WHEN 3 THEN "Touring Bike"
8> WHEN Null THEN "Something Else"
9> ELSE "(No Subcategory)"
10> END
11> FROM Employee
12> GO
ID Name SubCategory
----------- ---------- ----------------
1 Jason M
2 Robert Road Bike
3 Celia Touring Bike
4 Linda (No Subcategory)
5 David (No Subcategory)
6 James (No Subcategory)
7 Alison (No Subcategory)
8 Chris (No Subcategory)
9 Mary (No Subcategory)
(9 rows affected)
1>
2>
3> drop table employee
4> GO
1>
Case when with Mod and ABS()
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> -- A Searched CASE
3>
4> SELECT TOP 10 ID % 10 AS "ID",
5> "ID?" = CASE
6> WHEN (ID % 10) < 3 THEN "Ends With Less Than Three"
7> WHEN ID = 6 THEN "ProductID is 6"
8> WHEN ABS(ID % 10 - 2) <= 1 THEN "Based on calculation"
9> ELSE "More Than One Apart"
10> END
11> FROM Employee
12> GO
ID ID?
----------- -------------------------
1 Ends With Less Than Three
2 Ends With Less Than Three
3 Based on calculation
4 More Than One Apart
5 More Than One Apart
6 ProductID is 6
7 More Than One Apart
8 More Than One Apart
9 More Than One Apart
(9 rows affected)
1>
2>
3> drop table employee
4> GO
1>
Select case 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> DECLARE @Markup money
3> DECLARE @Multiplier money
4>
5> SELECT @Markup = .10 -- Change the markup here
6> SELECT @Multiplier = @Markup + 1 -- We want the end price, not the amount
7> -- of the increase, so add 1
8> SELECT ID, Name, salary,
9> Salary * @Multiplier AS "Marked Up salary", "New salary" =
10> CASE WHEN FLOOR(salary * @Multiplier + .24) > FLOOR(salary * @Multiplier)
11> THEN FLOOR(salary * @Multiplier) + .95
12> WHEN FLOOR(salary * @Multiplier + .5) > FLOOR(salary * @Multiplier)
13> THEN FLOOR(salary * @Multiplier) + .75
14> ELSE FLOOR(salary * @Multiplier) + .49
15> END
16> FROM Employee
17> GO
ID Name salary Marked Up salary New salary
----------- ---------- ----------- --------------------- ----------------------
1 Jason 40420 44462.0000 44462.4900
2 Robert 14420 15862.0000 15862.4900
3 Celia 24020 26422.0000 26422.4900
4 Linda 40620 44682.0000 44682.4900
5 David 80026 88028.6000 88028.7500
6 James 70060 77066.0000 77066.4900
7 Alison 90620 99682.0000 99682.4900
8 Chris 26020 28622.0000 28622.4900
9 Mary 60020 66022.0000 66022.4900
(9 rows affected)
1>
2> drop table employee
3> GO
1>
Use CASE in the UPDATE statement
27>
28> CREATE TABLE project (project_no CHAR(4) NOT NULL,
29> project_name CHAR(15) NOT NULL,
30> budget FLOAT NULL)
31>
32> insert into project values ("p1", "Search Engine", 120000.00)
33> insert into project values ("p2", "Programming", 95000.00)
34> insert into project values ("p3", "SQL", 186500.00)
35>
36> select * from project
37> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
project_no project_name budget
---------- --------------- ------------------------
p1 Search Engine 120000
p2 Programming 95000
p3 SQL 186500
(3 rows affected)
1>
2> -- Use CASE in the UPDATE statement.
3>
4> UPDATE project SET budget = CASE
5> WHEN budget > 0 and budget < 100000 THEN budget* 1.2
6> WHEN budget > = 100000 and budget < 200000 THEN budget* 1.1
7> ELSE budget* 1.05
8> END
9> GO
(3 rows affected)
1> select * from project
2> GO
project_no project_name budget
---------- --------------- ------------------------
p1 Search Engine 132000
p2 Programming 114000
p3 SQL 205150.00000000003
(3 rows affected)
1> drop table project
2> GO
1>
2>