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
<source lang="sql">
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>
</source>
CASE: return a specified value based on a set of business logic
<source lang="sql">
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>
</source>
Case when and else
<source lang="sql">
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>
</source>
Case when NULL
<source lang="sql">
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>
</source>
"Case when" statement in a procedure
<source lang="sql">
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>
</source>
Case When with else
<source lang="sql">
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>
</source>
Case when with Mod and ABS()
<source lang="sql">
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>
</source>
Select case statement
<source lang="sql">
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>
</source>
Use CASE in the UPDATE statement
<source lang="sql">
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>
</source>
