SQL Select 语句中的案例

Case in Select Statement

提问by fadzli feizal

I have an SQL statement that has a CASEfrom SELECTand I just can't get it right. Can you guys show me an example of CASEwhere the cases are the conditions and the results are from the cases. For example:

我有一个带有CASEfrom的 SQL 语句，但SELECT我做对了。你们能告诉我一个例子，CASE案例是条件，结果来自案例。例如：

     Select xxx, yyy
     case : desc case when bbb then 'blackberry';
     when sss then 'samsung';
     end 
     from (select ???? .....

where the results show

结果显示

 name                         age       handphone
xxx1                         yyy1      blackberry
xxx2                         yyy2      blackberry

回答by NuNn DaDdY

The MSDN is a good reference for these type of questions regarding syntax and usage. This is from the Transact SQL Reference - CASE page.

对于此类有关语法和用法的问题，MSDN 是一个很好的参考。这是来自 Transact SQL 参考 - 案例页面。

http://msdn.microsoft.com/en-us/library/ms181765.aspx

http://msdn.microsoft.com/en-us/library/ms181765.aspx

USE AdventureWorks2012;
GO
SELECT   ProductNumber, Name, "Price Range" = 
  CASE 
     WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
     WHEN ListPrice < 50 THEN 'Under '
     WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under 0'
     WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under 00'
     ELSE 'Over 00'
  END
FROM Production.Product
ORDER BY ProductNumber ;
GO

Another good site you may want to check out if you're using SQL Server is SQL Server Central. This has a large variety of resources available for whatever area of SQL Server you would like to learn.

如果您正在使用 SQL Server，您可能想要查看的另一个好站点是SQL Server Central。这为您想学习的 SQL Server 的任何领域提供了大量的资源。

回答by Sabir Al Fateh

I think these could be helpful for you .

我想这些可能对你有帮助。

Using a SELECTstatement with a simple CASEexpression

使用SELECT带有简单CASE表达式的语句

Within a SELECTstatement, a simple CASEexpression allows for only an equality check; no other comparisons are made. The following example uses the CASEexpression to change the display of product line categories to make them more understandable.

在一个SELECT语句中，一个简单的CASE表达式只允许进行相等性检查；没有进行其他比较。以下示例使用CASE表达式更改产品线类别的显示，使其更易于理解。

USE AdventureWorks2012;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Using a SELECTstatement with a searched CASEexpression

使用SELECT带有搜索CASE表达式的语句

Within a SELECTstatement, the searched CASEexpression allows for values to be replaced in the result set based on comparison values. The following example displays the list price as a text comment based on the price range for a product.

在SELECT语句中，搜索CASE表达式允许基于比较值替换结果集中的值。以下示例将标价显示为基于产品价格范围的文本注释。

USE AdventureWorks2012;
GO
SELECT   ProductNumber, Name, "Price Range" = 
      CASE 
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under '
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under 0'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under 00'
         ELSE 'Over 00'
      END
FROM Production.Product
ORDER BY ProductNumber ;
GO

Using CASEin an ORDER BYclause

使用CASE一个在ORDER BY条款

The following examples uses the CASEexpression in an ORDER BYclause to determine the sort order of the rows based on a given column value. In the first example, the value in the SalariedFlag column of the HumanResources.Employee table is evaluated. Employees that have the SalariedFlag set to 1 are returned in order by the BusinessEntityID in descending order. Employees that have the SalariedFlag set to 0 are returned in order by the BusinessEntityID in ascending order. In the second example, the result set is ordered by the column TerritoryName when the column CountryRegionName is equal to 'United States' and by CountryRegionName for all other rows.

以下示例使用子句中的CASE表达式ORDER BY根据给定的列值确定行的排序顺序。在第一个示例中，评估了 HumanResources.Employee 表的 SalariedFlag 列中的值。将 SalriedFlag 设置为 1 的员工按 BusinessEntityID 降序返回。SalriedFlag 设置为 0 的员工按 BusinessEntityID 升序返回。在第二个示例中，当 CountryRegionName 列等于 'United States' 时，结果集按 TerritoryName 列排序，所有其他行按 CountryRegionName 排序。

SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
GO


SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
         ELSE CountryRegionName END;

Using CASEin an UPDATEstatement

用CASE在UPDATE声明

The following example uses the CASEexpression in an UPDATEstatement to determine the value that is set for the column VacationHours for employees with SalariedFlag set to 0. When subtracting 10 hours from VacationHours results in a negative value, VacationHours is increased by 40 hours; otherwise, VacationHours is increased by 20 hours. The OUTPUTclause is used to display the before and after vacation values.

以下示例使用语句中的CASE表达式UPDATE来确定为 SalriedFlag 设置为 0 的员工的 VacationHours 列设置的值。当 VacationHours 减去 10 小时的结果为负值时，VacationHours 增加 40 小时；否则， VacationHours 将增加 20 小时。该OUTPUT子句用于显示假期前后的值。

USE AdventureWorks2012;
GO
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00)
       END
    )
OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue, 
       Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0; 

Using CASEin a HAVINGclause

使用CASE一个在HAVING条款

The following example uses the CASEexpression in a HAVINGclause to restrict the rows returned by the SELECTstatement. The statement returns the the maximum hourly rate for each job title in the HumanResources.Employee table. The HAVINGclause restricts the titles to those that are held by men with a maximum pay rate greater than 40 dollars or women with a maximum pay rate greater than 42 dollars.

以下示例使用子句中的CASE表达式HAVING来限制SELECT语句返回的行。该语句返回 HumanResources.Employee 表中每个职位的最高时薪。该HAVING条款将头衔限制为最高薪酬高于 40 美元的男性或最高薪酬高于 42 美元的女性持有的头衔。

USE AdventureWorks2012;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M' 
        THEN ph1.Rate 
        ELSE NULL END) > 40.00
     OR MAX(CASE WHEN Gender  = 'F' 
        THEN ph1.Rate  
        ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;

For more details description of these example visit the source.

有关这些示例的更多详细说明，请访问源。

Also visit hereand herefor some examples with great details.

还可以访问这里和这里获取一些非常详细的示例。

回答by user5035983

you can also use:

您还可以使用：

SELECT CASE
         WHEN upper(t.name) like 'P%' THEN
          'productive'
         WHEN upper(t.name) like 'T%' THEN
          'test'
         WHEN upper(t.name) like 'D%' THEN
          'development'
         ELSE
          'unknown'
       END as type
FROM table t