CASE:在sql server中的使用用法

CASE 语句在sql server跟其它程序语言中的switch功能类似，用于计算条件列表并返回多个可能结果表达式之一。
    在sql server中CASE具有两种格式：
    a.简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。     b.CASE 搜索函数计算一组布尔表达式以确定结果。
    以上两种格式都支持可选的 ELSE 参数。
   常见的几种CASE语句的用法如下所示:
   1.CASE 函数用于计算多个条件并为每个条件返回单个值。CASE 函数通常的用途是使用可读性更强的值替换代码或缩写。
下面的查询使用 CASE 函数重命名书籍的分类，以使之更易理解。
USE pubs
SELECT
   CASE type
      WHEN 'popular_comp' THEN 'Popular Computing'
      WHEN 'mod_cook' THEN 'Modern Cooking'
      WHEN 'business' THEN 'Business'
      WHEN 'psychology' THEN 'Psychology'
      WHEN 'trad_cook' THEN 'Traditional Cooking'
      ELSE 'Not yet categorized'
   END AS Category,
CONVERT(varchar(30), title) AS "Shortened Title",
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY 1
    2.使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句
    CASE 函数的另一个用途给数据分类。下面的查询使用 CASE 函数对价格分类。
SELECT
   CASE
      WHEN price IS NULL THEN 'Not yet priced'
      WHEN price < 10 THEN 'Very Reasonable Title'
      WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
      ELSE 'Expensive book!'
   END AS "Price Category",
CONVERT(varchar(20), title) AS "Shortened Title"
FROM pubs.dbo.titles
ORDER BY price
    3.使用带有 SUBSTRING 和 SELECT 的 CASE 函数
下面的示例使用 CASE 和 THEN 生成一个有关作者、图书标识号和每个作者所著图书类型的列表。
USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+
   RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id,
   Type =
CASE
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id
    4.在Group by子句中使用CASE子句
比方说下面的 GROUP BY 子句中的 CASE：
SELECT 'Number of Titles', Count(*)
FROM titles
GROUP BY
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END
GO
你甚至还可以组合这些选项，添加一个 ORDER BY 子句，如下所示：
USE pubs
GO
SELECT
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END AS Range,
    Title
FROM titles
GROUP BY
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END,
    Title
ORDER BY
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END,
    Title
GO
注意，为了在 GROUP BY 块中使用 CASE，查询语句需要在 GROUP BY 块中重复 SELECT 块中的 CASE 块。
1>
sqlStr = "select top " + PageSize + " *,(case c.TeacherScheduleID when '0' then '等待处理' when '" + TeacherID + "' then '你已选中' when '1' then '他人选中' end ) as Status from Booking_Detail as c inner join (select bookingdetailid from Booking_TeacherSchedule as a inner join (select [id] from Teacher_Schedule where userid=" + TeacherID + ") as b on a.teacherScheduleid=b.[id] and a.mark=1) as d on c.[id]=d.bookingdetailid where c.[id] not in (select top " + (int)(ViewState["CurrentPage"]) * PageSize + " id from Booking_Detail as c inner join (select bookingdetailid from Booking_TeacherSchedule as a inner join (select [id] from Teacher_Schedule where userid=2) as b on a.teacherScheduleid=b.[id] and a.mark=1) as d on c.[id]=d.bookingdetailid) order by c.[id] desc";
2>ALTER TABLE dbo.Customer ADD statedescription VARCHAR(50) NULL
　　GO
　　UPDATE dbo.Customer
　　SET stateDescription = CASE WHEN statecode = 'MA' THEN 'Massachusetts'
　　WHEN statecode = 'VA' THEN 'Virginia'
　　WHEN statecode = 'PA' THEN 'Pennsylvania'
　　ELSE NULL
　　END
3>SELECT COUNT(*) AS TotalCustomers,
　　SUM(CASE WHEN statecode = 'MA' THEN 1 ELSE NULL END) AS TotalMassCustomers,
　　AVG(CASE WHEN statecode = 'MA' THEN totalsales ELSE NULL END) AS TotalMassSales
　　FROM dbo.Customer
4>CREATE PROCEDURE dbo.getCustomerData @sortby VARCHAR(9), @sortdirection CHAR(4)
　　AS
　　SET nocount ON
　　SELECT customerid, firstname, lastname, statecode, statedescription, totalsales
　　FROM dbo.Customer
　　ORDER BY
　　CASE @sortdirection
　　WHEN 'asc' THEN
　　CASE @sortby
　　WHEN 'firstname' THEN firstname
　　WHEN 'lastname' THEN lastname
　　END
　　END
　　ASC,
　　CASE @sortdirection
　　WHEN 'desc' THEN
　　CASE @sortby
　　WHEN 'firstname' THEN firstname
　　WHEN 'lastname' THEN lastname
　　END
　　END
　　DESC
　　GO
　　EXEC dbo.getCustomerData 'lastname', 'desc'
5>ALTER PROCEDURE dbo.getCustomerData @sortby VARCHAR(9), @sortdirection CHAR(4), @statecode VARCHAR(2) = NULL
　　AS
　　SET nocount ON
　　SELECT customerid, firstname, lastname, statecode, statedescription, totalsales
　　FROM dbo.Customer
　　WHERE statecode = CASE WHEN @statecode IS NOT NULL THEN @statecode
　　ELSE statecode
　　END
　　ORDER BY
　　CASE @sortdirection
　　WHEN 'asc' THEN
　　CASE @sortby
　　WHEN 'firstname' THEN firstname
　　WHEN 'lastname' THEN lastname
　　END
　　END
　　ASC,
　　CASE @sortdirection
　　WHEN 'desc' THEN
　　CASE @sortby
　　WHEN 'firstname' THEN firstname
　　WHEN 'lastname' THEN lastname
　　END
　　END
　　DESC
　　GO
　　EXEC dbo.getCustomerData 'lastname', 'desc', 'MA'