SQL Server/T-SQL Tutorial/Query/Case
Содержание
- 1 Alias for case statement
- 2 A SELECT statement that uses a searchable CASE function
- 3 A SELECT statement that uses a simple CASE function
- 4 A Simple CASE
- 5 Automatic Code Generation for CASE Expressions
- 6 CASE Expression
- 7 Case then with query
- 8 Case when else
- 9 Case with range
- 10 Doing a calculation in a Searched CASE
- 11 Searched CASE expression looks for the first expression that evaluates to true.
- 12 Summarizing Data Using the CASE Expression
- 13 Summarizing Data Using the Searched CASE Expression
- 14 The syntax of the searched CASE function
- 15 The syntax pattern for case
- 16 Update statement based on case condition
- 17 Use two case statements in one select statement
- 18 Using a CASE expression to sum sales by weekday.
- 19 Using the CASE Expression with Complex Conditions
Alias for case statement
4> CREATE TABLE Product(
5> ProductID int NOT NULL,
6> Name nvarchar(25) NOT NULL,
7> ProductNumber nvarchar(25) ,
8> Color nvarchar(15) NULL,
9> StandardCost money NOT NULL,
10> Size nvarchar(5) NULL,
11> Weight decimal(8, 2) NULL,
12> ProductLine nchar(20) NULL,
13> SellStartDate datetime NOT NULL,
14> SellEndDate datetime NULL
15> )
16> GO
1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22");
2> GO
(1 rows affected)
1> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22");
2> GO
(1 rows affected)
1> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22");
2> GO
(1 rows affected)
1> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22");
2> GO
(1 rows affected)
1> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22");
2> GO
(1 rows affected)
1> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22");
2> GO
(1 rows affected)
1> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22");
2> GO
(1 rows affected)
1> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22");
2> GO
(1 rows affected)
1> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22");
2> GO
(1 rows affected)
1> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22");
2> GO
(1 rows affected)
1>
2>
3>
4> SELECT ProductID
5> , Name
6> , CASE ProductID
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 SubCategory
13> FROM Product
14> GO
ProductID Name SubCategory
----------- ------------------------- -----------
1 Product A Mountain
2 Product B Road
3 Product C Touring
4 Product D No
5 Product E No
6 Product F No
7 Product G No
8 Product H No
9 Product I No
0 Product J No
(10 rows affected)
1>
2>
3> drop table Product;
4> GO
A SELECT statement that uses a searchable CASE function
4>
5> create table Billings (
6> BankerID INTEGER,
7> BillingNumber INTEGER,
8> BillingDate datetime,
9> BillingTotal INTEGER,
10> TermsID INTEGER,
11> BillingDueDate datetime ,
12> PaymentTotal INTEGER,
13> CreditTotal INTEGER
14>
15> );
16> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3> SELECT BillingNumber, BillingTotal, BillingDate, BillingDueDate,
4> CASE
5> WHEN DATEDIFF(day, BillingDueDate, GETDATE()) > 30
6> THEN "Over 30 days past due"
7> WHEN DATEDIFF(day, BillingDueDate, GETDATE()) > 0
8> THEN "1 to 30 days past due"
9> ELSE "Current"
10> END AS Status
11> FROM Billings
12> WHERE BillingTotal - PaymentTotal - CreditTotal > 0
13> GO
BillingNumber BillingTotal BillingDate BillingDueDate Status
------------- ------------ ----------------------- ----------------------- ---------------------
(0 rows affected)
1>
2>
3> drop table Billings;
4> GO
1>
A SELECT statement that uses a simple CASE function
5>
6> create table Billings (
7> BankerID INTEGER,
8> BillingNumber INTEGER,
9> BillingDate datetime,
10> BillingTotal INTEGER,
11> TermsID INTEGER,
12> BillingDueDate datetime ,
13> PaymentTotal INTEGER,
14> CreditTotal INTEGER
15>
16> );
17> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3> SELECT BillingNumber, TermsID,
4> CASE TermsID
5> WHEN 1 THEN "Net due 10 days"
6> WHEN 2 THEN "Net due 20 days"
7> WHEN 3 THEN "Net due 30 days"
8> WHEN 4 THEN "Net due 60 days"
9> WHEN 5 THEN "Net due 90 days"
10> END AS Terms
11> FROM Billings
12> GO
BillingNumber TermsID Terms
------------- ----------- ---------------
1 1 Net due 10 days
2 1 Net due 10 days
3 1 Net due 10 days
4 1 Net due 10 days
5 1 Net due 10 days
6 1 Net due 10 days
7 1 Net due 10 days
8 1 Net due 10 days
9 1 Net due 10 days
0 1 Net due 10 days
(10 rows affected)
1>
2>
3> drop table Billings;
4> GO
A Simple CASE
5>
6>
7> CREATE TABLE Orders (
8> OrderID int NOT NULL ,
9> CustomerID nchar (5) NULL ,
10> EmployeeID int NULL ,
11> OrderDate datetime NULL ,
12> RequiredDate datetime NULL ,
13> ShippedDate datetime NULL ,
14> ShipVia int NULL ,
15> Freight money NULL DEFAULT (0),
16> ShipName nvarchar (40) NULL ,
17> ShipAddress nvarchar (60) NULL ,
18> ShipCity nvarchar (15) NULL ,
19> ShipRegion nvarchar (15) NULL ,
20> ShipPostalCode nvarchar (10) NULL ,
21> ShipCountry nvarchar (15) NULL)
22> GO
1>
2>
3> SELECT TOP 10 OrderID, OrderID % 10 AS "Last Digit", Position =
4> CASE OrderID % 10
5> WHEN 1 THEN "First"
6> WHEN 2 THEN "Second"
7> WHEN 3 THEN "Third"
8> WHEN 4 THEN "Fourth"
9> ELSE "Something Else"
10> END
11> FROM Orders
12> GO
OrderID Last Digit Position
----------- ----------- --------------
(0 rows affected)
1>
2> drop table orders;
3> GO
Automatic Code Generation for CASE Expressions
6>
7>
8> CREATE TABLE titles(
9> title_id varchar(20),
10> title varchar(80) NOT NULL,
11> type char(12) NOT NULL,
12> pub_id char(4) NULL,
13> price money NULL,
14> advance money NULL,
15> royalty int NULL,
16> ytd_sales int NULL,
17> notes varchar(200) NULL,
18> pubdate datetime NOT NULL
19> )
20> 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> select distinct "WHEN """
2> + SUBSTRING(title_id,1,2)
3> + """ THEN" from titles
4> GO
--------------
WHEN "1" THEN
WHEN "2" THEN
WHEN "3" THEN
WHEN "4" THEN
WHEN "5" THEN
WHEN "6" THEN
WHEN "7" THEN
WHEN "8" THEN
(8 rows affected)
1>
2> drop table titles;
3> GO
1>
2>
CASE Expression
5>
6> CREATE TABLE titles(
7> title_id varchar(20),
8> title varchar(80) NOT NULL,
9> type char(12) NOT NULL,
10> pub_id char(4) NULL,
11> price money NULL,
12> advance money NULL,
13> royalty int NULL,
14> ytd_sales int NULL,
15> notes varchar(200) NULL,
16> pubdate datetime NOT NULL
17> )
18> 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> SELECT title_id,
4> CASE SUBSTRING(title_id,1,2)
5> WHEN "BU" THEN "Business"
6> WHEN "MC" THEN "Modern Cooking"
7> WHEN "PC" THEN "Popular Computing"
8> WHEN "PS" THEN "Psychology"
9> WHEN "TC" THEN "Traditional Cooking"
10> END AS BookType
11> FROM titles
12> GO
title_id BookType
-------------------- -------------------
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
(8 rows affected)
1>
2> drop table titles;
3> GO
1>
Case then with query
3> CREATE TABLE titles(
4> title_id varchar(20),
5> title varchar(80) NOT NULL,
6> type char(12) NOT NULL,
7> pub_id char(4) NULL,
8> price money NULL,
9> advance money NULL,
10> royalty int NULL,
11> ytd_sales int NULL,
12> notes varchar(200) NULL,
13> pubdate datetime NOT NULL
14> )
15> 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> SELECT title, pub_id,
4> CASE WHEN price IS NULL THEN (SELECT MIN(price) FROM titles)
5> ELSE price
6> END
7> FROM titles
8>
9> 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
1>
2>
Case when else
7> CREATE TABLE Product(
8> ProductID int NOT NULL,
9> Name nvarchar(25) NOT NULL,
10> ProductNumber nvarchar(25) ,
11> Color nvarchar(15) NULL,
12> StandardCost money NOT NULL,
13> Size nvarchar(5) NULL,
14> Weight decimal(8, 2) NULL,
15> ProductLine nchar(20) NULL,
16> SellStartDate datetime NOT NULL,
17> SellEndDate datetime NULL
18> )
19> GO
1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22");
2> GO
(1 rows affected)
1>
2>
3> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22");
4> GO
(1 rows affected)
1>
2>
3> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22");
4> GO
(1 rows affected)
1>
2>
3>
4>
5>
6> SELECT ProductID
7> , Name
8> , SubCategory = CASE ProductID
9> WHEN 1 THEN "Mountain Bike"
10> WHEN 2 THEN "Road Bike"
11> WHEN 3 THEN "Touring Bike"
12> WHEN Null THEN "Something Else"
13> ELSE "(No Subcategory)"
14> END
15> FROM Product
16> GO
ProductID Name SubCategory
----------- ------------------------- ----------------
1 Product A Mountain Bike
2 Product B Road Bike
3 Product C Touring Bike
4 Product D (No Subcategory)
5 Product E (No Subcategory)
6 Product F (No Subcategory)
7 Product G (No Subcategory)
8 Product H (No Subcategory)
9 Product I (No Subcategory)
0 Product J (No Subcategory)
(10 rows affected)
1>
2>
3> drop table Product;
4> GO
Case with range
5>
6>
7> CREATE TABLE titles(
8> title_id varchar(20),
9> title varchar(80) NOT NULL,
10> type char(12) NOT NULL,
11> pub_id char(4) NULL,
12> price money NULL,
13> advance money NULL,
14> royalty int NULL,
15> ytd_sales int NULL,
16> notes varchar(200) NULL,
17> pubdate datetime NOT NULL
18> )
19> 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> SELECT
3> title,
4> price,
5> "classification"=CASE
6> WHEN price < 10.00 THEN "Low Priced"
7> WHEN price BETWEEN 10.00 AND 20.00 THEN "Moderately Priced"
8> WHEN price > 20.00 THEN "Expensive"
9> ELSE "Unknown"
10> END
11> FROM titles
12> GO
title price classification
-------------------------------------------------------------------------------- --------------------- -----------------
Secrets 20.0000 Moderately Priced
The 19.9900 Moderately Priced
Emotional 7.9900 Low Priced
Prolonged 19.9900 Moderately Priced
With 11.9500 Moderately Priced
Valley 19.9900 Moderately Priced
Any? 14.9900 Moderately Priced
Fifty 11.9500 Moderately Priced
(8 rows affected)
1>
2>
3> drop table titles;
4> GO
1>
2>
Doing a calculation in a Searched CASE
5>
6>
7> CREATE TABLE OrderDetails (
8> OrderID int NOT NULL ,
9> ProductID int NOT NULL ,
10> UnitPrice money NOT NULL DEFAULT (0),
11> Quantity smallint NOT NULL DEFAULT (1),
12> Discount real NOT NULL DEFAULT (0)
13> )
14> GO
1> INSERT OrderDetails VALUES(10248,11,14,12,0)
2> INSERT OrderDetails VALUES(10248,42,9.8,10,0)
3> INSERT OrderDetails VALUES(10248,72,34.8,5,0)
4> INSERT OrderDetails VALUES(10249,14,18.6,9,0)
5> INSERT OrderDetails VALUES(10249,51,42.4,40,0)
6> INSERT OrderDetails VALUES(10250,41,7.7,10,0)
7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15)
8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15)
9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05)
10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05)
11> 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>
2>
3> SELECT TOP 10 OrderID % 10 AS "Last Digit",
4> ProductID,
5> "How Close?" = CASE
6> WHEN (OrderID % 10) < 3 THEN "Ends With Less Than Three"
7> WHEN ProductID = 6 THEN "ProductID is 6"
8> WHEN ABS(OrderID % 10 - ProductID) <= 1 THEN "Within 1"
9> ELSE "More Than One Apart"
10> END
11> FROM OrderDetails
12> WHERE ProductID < 10
13> ORDER BY OrderID DESC
14> GO
Last Digit ProductID How Close?
----------- ----------- -------------------------
(0 rows affected)
1>
2> drop table OrderDetails;
3> GO
Searched CASE expression looks for the first expression that evaluates to true.
If none of the WHEN conditions evaluates to true, the value of the ELSE expression is returned.
9> CREATE TABLE employee(
10> id INTEGER NOT NULL PRIMARY KEY,
11> first_name VARCHAR(10),
12> last_name VARCHAR(10),
13> salary DECIMAL(10,2),
14> start_Date DATETIME,
15> region VARCHAR(10),
16> city VARCHAR(20),
17> managerid INTEGER
18> );
19> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> SELECT first_name,
5> CASE
6> WHEN salary > 0 AND salary < 100000 THEN 1
7> WHEN salary >= 100000 AND salary < 200000 THEN 2
8> WHEN salary >= 200000 AND salary < 300000 THEN 3
9> ELSE 4
10> END budget_weight
11> FROM Employee
12> GO
first_name budget_weight
---------- -------------
Jason 1
Alison 1
James 1
Celia 1
Robert 1
Linda 1
David 1
James 1
Joan 1
(9 rows affected)
1>
2>
3> drop table employee;
4> GO
Summarizing Data Using the CASE Expression
Selecting the total sales by weekday--simple query.
8>
9> CREATE TABLE sales(
10> stor_id char(4) NOT NULL,
11> ord_num varchar(20) NOT NULL,
12> ord_date datetime NOT NULL,
13> qty smallint NOT NULL,
14> payterms varchar(12) NOT NULL,
15> title_id varchar(80)
16> )
17> GO
1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1")
3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2")
4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3")
5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT sum(qty) AS Total,
4> datename(weekday, ord_date) AS Weekday
5> FROM sales
6> GROUP BY datename(weekday, ord_date)
7> GO
Total Weekday
----------- ------------------------------
75 Tuesday
80 Wednesday
(2 rows affected)
1>
2> drop table sales;
3> GO
1>
Summarizing Data Using the Searched CASE Expression
6>
7> CREATE TABLE stores(
8> stor_id char(4) NOT NULL,
9> stor_name varchar(40) NULL,
10> stor_address varchar(40) NULL,
11> city varchar(20) NULL,
12> state char(2) NULL,
13> zip char(5) NULL
14> )
15> GO
1> insert stores values("1","B","567 Ave.","Tustin", "CA","92789")
2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745")
3> insert stores values("3","T","679 St.", "Portland", "OR","89076")
4> insert stores values("4","F","89 St.", "Fremont", "CA","90019")
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE TABLE sales(
3> stor_id char(4) NOT NULL,
4> ord_num varchar(20) NOT NULL,
5> ord_date datetime NOT NULL,
6> qty smallint NOT NULL,
7> payterms varchar(12) NOT NULL,
8> title_id varchar(80)
9> )
10> GO
1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1")
3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2")
4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3")
5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT DISTINCT st.stor_name,
3> "Sales Rating" =
4> CASE
5> WHEN (SELECT SUM(sa.qty) FROM sales sa
6> WHERE st.stor_id = sa.stor_id) < 10
7> THEN "Poor"
8> WHEN (SELECT SUM(sa.qty) FROM sales sa
9> WHERE st.stor_id = sa.stor_id) < 80
10> THEN "Average"
11> WHEN (SELECT SUM(sa.qty) FROM sales sa
12> WHERE st.stor_id = sa.stor_id) < 100
13> THEN "Good"
14> ELSE "Excellent"
15> END,
16> "Sales Total" = (SELECT SUM(sa.qty) FROM sales sa
17> WHERE st.stor_id = sa.stor_id)
18> FROM stores st, sales sa
19> ORDER BY "Sales Total"
20> GO
stor_name Sales Rating Sales Total
---------------------------------------- ------------ -----------
N Average 10
T Average 20
F Average 25
B Average 75
(4 rows affected)
1> drop table stores;
2> drop table sales;
3> GO
1>
2>
The syntax of the searched CASE function
CASE
WHEN conditional_expression_1 THEN result_expression_1
[WHEN conditional_expression_2 THEN result_expression_2]...
[ELSE else_result_expression]
END
The syntax pattern for case
SELECT CASE value to evaluate
WHEN literal value 1 THEN return value
WHEN literal value 2 THEN return value
...
END
10>
11>
12> DECLARE @Status Int
13> SET @Status = 1
14> SELECT CASE @Status
15> WHEN 1 THEN "Active"
16> WHEN 2 THEN "Inactive"
17> WHEN 3 THEN "Pending"
18> END
19> GO
--------
Active
(1 rows affected)
Update statement based on case condition
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20),
13> managerid INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2> UPDATE Employee
3> SET salary = CASE
4> WHEN salary > 0 and salary < 100000 THEN salary* 1.2
5> WHEN salary > = 100000 and salary < 200000 THEN salary* 1.1
6> ELSE salary* 1.05
7> END
8> GO
(9 rows affected)
1>
2>
3> drop table employee;
4> GO
Use two case statements in one select statement
21> create table Bankers(
22> BankerID Integer,
23> BankerName VARCHAR(20),
24> BankerContactLName VARCHAR(20),
25> BankerContactFName VARCHAR(20),
26> BankerCity VARCHAR(20),
27> BankerState VARCHAR(20),
28> BankerZipCode VARCHAR(20),
29> BankerPhone VARCHAR(20)
30> )
31> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3>
4>
5> SELECT
6> CASE
7> WHEN GROUPING(BankerState) = 1 THEN "All"
8> ELSE BankerState
9> END AS BankerState,
10> CASE
11> WHEN GROUPING(BankerCity) = 1 THEN "All"
12> ELSE BankerCity
13> END AS BankerCity,
14> COUNT(*) AS QtyBankers
15> FROM Bankers
16> WHERE BankerState IN ("IA", "NJ")
17> GROUP BY BankerState, BankerCity WITH ROLLUP
18> ORDER BY BankerState DESC, BankerCity DESC
19> GO
BankerState BankerCity QtyBankers
-------------------- -------------------- -----------
(0 rows affected)
1>
2> drop table Bankers;
3> GO
1>
2>
Using a CASE expression to sum sales by weekday.
4>
5>
6> CREATE TABLE sales(
7> stor_id char(4) NOT NULL,
8> ord_num varchar(20) NOT NULL,
9> ord_date datetime NOT NULL,
10> qty smallint NOT NULL,
11> payterms varchar(12) NOT NULL,
12> title_id varchar(80)
13> )
14> GO
1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1")
3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2")
4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3")
5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT sum(qty) AS Total,
4> CASE datepart(weekday, ord_date)
5> WHEN 1 THEN "Sunday"
6> WHEN 2 THEN "Monday"
7> WHEN 3 THEN "Tuesday"
8> WHEN 4 THEN "Wednesday"
9> WHEN 5 THEN "Thursday"
10> WHEN 6 THEN "Friday"
11> WHEN 7 THEN "Saturday"
12> END AS Weekday
13> FROM sales
14> GROUP BY datepart(weekday, ord_date)
15> GO
Total Weekday
----------- ---------
75 Tuesday
80 Wednesday
(2 rows affected)
1> drop table sales;
2> GO
Using the CASE Expression with Complex Conditions
5>
6> CREATE TABLE titles(
7> title_id varchar(20),
8> title varchar(80) NOT NULL,
9> type char(12) NOT NULL,
10> pub_id char(4) NULL,
11> price money NULL,
12> advance money NULL,
13> royalty int NULL,
14> ytd_sales int NULL,
15> notes varchar(200) NULL,
16> pubdate datetime NOT NULL
17> )
18> 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> SELECT
3> title_id, price, ytd_sales,
4> "DiscountPrice" =
5> CASE
6> WHEN ytd_sales < 1000 THEN
7> CONVERT (SMALLMONEY, price * .50)
8> WHEN ytd_sales < 3000 THEN
9> CONVERT (SMALLMONEY, price * .85)
10> ELSE price
11> END
12> FROM titles
13> GO
title_id price ytd_sales DiscountPrice
-------------------- --------------------- ----------- ---------------------
1 20.0000 4095 20.0000
2 19.9900 4095 19.9900
3 7.9900 3336 7.9900
4 19.9900 4072 19.9900
5 11.9500 3876 11.9500
6 19.9900 2032 16.9915
7 14.9900 4095 14.9900
8 11.9500 1509 10.1575
(8 rows affected)
1>
2> drop table titles;
3> GO
1>
2>