Select 문의 사례

Select 문의 사례

---

CASEfrom 이있는 SQL 문이 있는데 SELECT제대로 얻을 수 없습니다. CASE사례가 조건이고 결과가 사례에서 나온 예를 보여줄 수 있습니까? 예를 들면 다음과 같습니다.

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

결과가 나타나는 곳

 name                         age       handphone
xxx1                         yyy1      blackberry
xxx2                         yyy2      blackberry

---

MSDN은 구문과 사용법에 관한 이러한 유형의 질문에 대한 좋은 참고 자료입니다. Transact SQL 참조-CASE 페이지에서 가져온 것입니다.

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 $50'
     WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
     WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
     ELSE 'Over $1000'
  END
FROM Production.Product
ORDER BY ProductNumber ;
GO

SQL Server를 사용하고 있는지 확인하고 싶은 또 다른 좋은 사이트는 SQL Server Central 입니다. 여기에는 학습하려는 SQL Server 영역에 사용할 수있는 다양한 리소스가 있습니다.

---

이것들이 당신에게 도움이 될 것이라고 생각합니다.

SELECT간단한 CASE표현 으로 문장 사용하기

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

SELECT검색된 CASE표현식 으로 명령문 사용

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 $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber ;
GO

절 CASE에서 사용ORDER BY

The following examples uses the CASE expression in an ORDER BY clause 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.

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 CASE in an UPDATE statement

The following example uses the CASE expression in an UPDATE statement 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 OUTPUT clause is used to display the before and after vacation values.

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 CASE in a HAVING clause

The following example uses the CASE expression in a HAVING clause to restrict the rows returned by the SELECT statement. The statement returns the the maximum hourly rate for each job title in the HumanResources.Employee table. The HAVING clause 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.

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 here and here for some examples with great details.

---

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

참고URL : https://stackoverflow.com/questions/14189216/case-in-select-statement