[SQL] T-SQL 조건부 주문

T-SQL 조건부 주문

나는 사용자가 선택한 및 SQL 매개 변수로 전달 된 정렬 순서와 정렬 방향과 개체의 목록을 반환하는 저장 프로 시저를 작성하려합니다.

PRODUCT_ID (INT), 이름 (VARCHAR), 값 (INT), CREATED_DATE (날짜) : 나는 다음과 같은 열이있는 제품의 테이블이 있다고 가정하자 및 매개 변수 @sortDir 및 @sortOrder

내가 좋아하는 뭔가를하고 싶어

select *
from Product
  if (@sortOrder = 'name' and @sortDir = 'asc') 
  then order by name asc
  if (@sortOrder = 'created_date' and @sortDir = 'asc') 
  then order by created_date asc
  if (@sortOrder = 'name' and @sortDir = 'desc') 
  then order by name desc
  if (@sortOrder = 'created_date' and @sortDir = 'desc') 
  then order by created_date desc

나는 case 문으로 그것을 시도했지만 데이터 유형이 다른 때부터 문제가 발생했다. 누구나 어떤 제안을 얻었다?

해결법

1. ==============================

   1.CASE는 값을 반환하는 식입니다. 그것은 IF 같은 흐름 제어 아닙니다. 그리고 당신은 쿼리 내에서 IF를 사용할 수 없습니다.

   CASE는 값을 반환하는 식입니다. 그것은 IF 같은 흐름 제어 아닙니다. 그리고 당신은 쿼리 내에서 IF를 사용할 수 없습니다.

   불행하게도, 당신이 원하는 일을 복잡하게 CASE 식에는 제한 사항이 몇 가지 있습니다. 예를 들어, CASE 식의 지점 모두 동일한 형식을 반환하거나 동일한 형식으로 암시 적으로 변환해야합니다. 나는 문자열과 날짜로 그 시도하지 않을 것입니다. 또한 정렬 방향을 지정하는 CASE를 사용할 수 없습니다.

   SELECT column_list_please
   FROM dbo.Product -- dbo prefix please
   ORDER BY 
     CASE WHEN @sortDir = 'asc' AND @sortOrder = 'name' THEN name END,
     CASE WHEN @sortDir = 'asc' AND @sortOrder = 'created_date' THEN created_date END,
     CASE WHEN @sortDir = 'desc' AND @sortOrder = 'name' THEN name END DESC,
     CASE WHEN @sortDir = 'desc' AND @sortOrder = 'created_date' THEN created_date END DESC;
   

   (이 더욱 복잡해 특히)는 틀림없이 쉽게 솔루션은 동적 SQL을 사용하는 것입니다. 방해 SQL 주입하려면 값을 테스트 할 수 있습니다 :

   IF @sortDir NOT IN ('asc', 'desc')
     OR @sortOrder NOT IN ('name', 'created_date')
   BEGIN
     RAISERROR('Invalid params', 11, 1);
     RETURN;
   END
   
   DECLARE @sql NVARCHAR(MAX) = N'SELECT column_list_please
     FROM dbo.Product ORDER BY ' + @sortOrder + ' ' + @sortDir;
   
   EXEC sp_executesql @sql;
   

   모든에도 불구하고 동적 SQL에 대한 또 다른 플러스는 두려움에 눈먼 그것에 대해 확산입니다 : 대신 처음 사용하는 일이 어떤 종류의 변화에 ​​최적화 하나 개의 계획을 각 종류의 변화에 ​​가장 적합한 계획을 얻을 수 있습니다. 그것은 또한 내가 달릴 최근의 성능 비교에서 가장 보편적으로 수행 :

   http://sqlperformance.com/conditional-order-by

2. ==============================

   2.나는 여러 case 문을 사용하지만 당신은 case 문이 필요합니다 :

   나는 여러 case 문을 사용하지만 당신은 case 문이 필요합니다 :

   order by (case when @sortOrder = 'name' and @sortDir = 'asc' then name end)  asc,
            (case when @sortOrder = 'name' and @sortDir = 'desc' then name end) desc,
            (case when @sortOrder = 'created_date' and @sortDir = 'asc' then created_date end) asc,
            (case when @sortOrder = 'created_date' and @sortDir = 'desc' then created_date end) desc
   

   네 가지 조항을 갖는 유형 사이의 변환 문제를 제거합니다.

3. ==============================

   3.이 일을 여러 가지 방법이 있습니다. 한 가지 방법은 다음과 같습니다

   이 일을 여러 가지 방법이 있습니다. 한 가지 방법은 다음과 같습니다

   SELECT *
   FROM
   (
     SELECT
     ROW_NUMBER() OVER ( ORDER BY
     CASE WHEN @sortOrder = 'name' and @sortDir = 'asc' then name
     END ASC,
     CASE WHEN @sortOrder = 'name' and @sortDir = 'desc' THEN name
     END DESC,
     CASE WHEN i(@sortOrder = 'created_date' and @sortDir = 'asc' THEN created_date
     END ASC,
     CASE WHEN i(@sortOrder = 'created_date' and @sortDir = 'desc' THEN created_date
     END ASC) RowNum
     *
   )
   order by 
   RowNum
   

   또한 동적 SQL을 사용하여 작업을 수행 할 수 있습니다.

4. ==============================

   4.

   declare @str varchar(max)
   set @str = 'select * from Product order by ' + @sortOrder + ' ' + @sortDir
   exec(@str)
   

5. ==============================

   5.나는 비슷한 일을하려고 할 때 나는 그러나 내가 생성 된 선택 문이 심각 동적 SQL 접근 방법을 사용하는 내 능력을 제한 오히려 큰 못생긴 문자열 연결을 포함,이에 비틀 거렸다. 당신이 간단한 검색의 경우이있는 경우,이 괜찮은 솔루션입니다.

   나는 비슷한 일을하려고 할 때 나는 그러나 내가 생성 된 선택 문이 심각 동적 SQL 접근 방법을 사용하는 내 능력을 제한 오히려 큰 못생긴 문자열 연결을 포함,이에 비틀 거렸다. 당신이 간단한 검색의 경우이있는 경우,이 괜찮은 솔루션입니다.

   존재가 말했다, SQL 서버에서이 작업을 수행하는 또 다른 방법은 특별히 테이블 변수입니다. 이것은 더 많은 오버 헤드와 복잡성이 필요하지만 그것은 또한 당신에게 많은 유연성을 제공합니다.

   DECLARE @RawProducts TABLE (
       product_id int, 
       name varchar(50), 
       value int, 
       created_date datetime
   )
   INSERT INTO @RawProducts
   SELECT * FROM [Product]
   
   IF @sortOrder = 'name' AND @sortDir = 'asc' BEGIN
       SELECT * FROM @RawProducts
       ORDER BY [name] ASC
   END
   
   IF @sortOrder = 'name' AND @sortDir = 'desc' BEGIN
       SELECT * FROM @RawProducts
       ORDER BY [name] desc
   END
   
   IF @sortOrder = 'created_date' AND @sortDir = 'asc' BEGIN
       SELECT * FROM @RawProducts
       ORDER BY [created_date] ASC
   END
   
   IF @sortOrder = 'created_date' AND @sortDir = 'desc' BEGIN
       SELECT * FROM @RawProducts
       ORDER BY [created_date] desc
   END
   

   이 방법에 대한 한 가지 단점은 당신에 의해 주문 원하는 각각의 경우 블록 및 모든 경우를 추가해야합니다하지만 당신은 동적 SQL을 포함하지 않는 솔루션을 그렇게해야한다는 것입니다.

   당신은 비교적 작은 데이터 세트가 있고 분별 검색의 경우에 쉽게 가지고 있다면이 방법을 추천 할 것입니다 유일한 시간이다.

from https://stackoverflow.com/questions/15621609/t-sql-conditional-order-by by cc-by-sa and MIT license