如何订购2个SQL字段在asc和desc动态
我想订购一个SQL Select查询哪里有2个字段按顺序排列。然后我需要决定一个是降序而另一个是升序。如何做到这一点如何订购2个SQL字段在asc和desc动态
我想是这样的:
Select * from Customer
Order By Date @asc_or_Desc_date, Name @asc_or_Desc_name
任何人有什么想法?
我都试过,但它似乎无法
SELECT
Customer_ID,
Name,
Age
FROM #Customer
ORDER BY
CASE WHEN @fieldSort ='Name'
THEN ROW_NUMBER() over (order by Name) *
case when @directionOfSort = 'A'
THEN 1 ELSE -1 END,
ROW_NUMBER() over (order by Age) *
case when @directionOfSort = 'A'
THEN 1 ELSE -1 END,
END
任何人都知道如何排序呢?
你将不得不为了使用一个变量动态创建SQL语句:
DECLARE @asc_desc VARCHAR(4);
SET @asc_desc = 'DESC';
DECLARE @sql NVARCHAR(1000);
SET @sql = 'Select * from Customer Order By Date ' + @asc_desc + ', Name';
EXEC sp_executesql @sql
这将订单日期DESCENDING
和名称ASCENDING
。
如果您想使用DESCENDING
,您只需要添加DESC
,因为ASCENDING
是默认值。
在这种情况下,`sp_execute`需要`EXEC`。 – 2011-02-14 14:34:10
@Andriy M:是的,我试图在SSMS中运行代码以及纠正问题。 – 2011-02-14 14:35:22
注意:在执行此操作时应非常了解SQL注入。参数需要由您自己的代码提供,和/或需要验证的值。如果你在一个存储过程中做这件事,请检查它们在存储过程中,只是为了确定。 – MatBailie 2011-02-14 15:12:36
在SQL Server 2005 +,你可以采用以下设备:
WITH CustomerCTE AS (
SELECT
*,
DateSort = ROW_NUMBER() OVER (ORDER BY Date),
NameSort = ROW_NUMBER() OVER (ORDER BY Name)
FROM Customer
)
SELECT *
FROM CustomerCTE
ORDER BY DateSort * @DateSortDir, NameSort * @NameSortDir
在这种情况下,增值经销商应该是1
或-1
。
编辑
的额外贴例子似乎暗示列的顺序,以便使用的应该是动力了。现在看来,这两个列的顺序方向是一致的。
无论是否如此(问题变得更加模糊),两者都在我的第二个解决方案中被假定。
DECLARE @IntSortDir int;
SET @IntSortDir = CASE @directionOfSort WHEN 'A' THEN 1 ELSE -1 END;
WITH CustomerCTE AS (
SELECT
Customer_ID,
Name,
Age,
NameSort = ROW_NUMBER() OVER (ORDER BY Name),
AgeSort = ROW_NUMBER() OVER (ORDER BY Date)
FROM Customer
)
SELECT
Customer_ID,
Name,
Age
FROM CustomerCTE
ORDER BY
CASE @fieldSort WHEN 'Age' THEN AgeSort END * @IntSortDir,
NameSort * @directionOfSort,
CASE @fieldSort WHEN 'Name' THEN AgeSort END * @IntSortDir
@fieldSort
指定主要订单栏。另一个自动成为第二个。
SELECT
Customer_ID,
Name,
Age
FROM
#Customer
ORDER BY
CASE WHEN @field = 'Name' AND @direction = 'A' THEN Name ELSE NULL END ASC,
CASE WHEN @field = 'Name' AND @direction = 'D' THEN Name ELSE NULL END DESC,
CASE WHEN @field = 'Age' AND @direction = 'A' THEN Age ELSE NULL END ASC,
CASE WHEN @field = 'Age' AND @direction = 'D' THEN Age ELSE NULL END DESC
我不想这样做,在许多不同的组合虽然。如果你有很多的组合我会做somethign基于以下...
SELECT
Customer_ID,
Name,
Age
FROM
(
SELECT
Customer_ID,
Name,
ROW_NUMBER() OVER (ORDER BY Name) AS "name_order",
Age,
ROW_NUMBER() OVER (ORDER BY Age) AS "age_order"
FROM
#Customer
)
AS [data]
ORDER BY
CASE @field1
WHEN 'Name' THEN CASE @direction1 WHEN 'A' THEN name_order ELSE -name_order END
WHEN 'Age' THEN CASE @direction1 WHEN 'A' THEN age_order ELSE -age_order END
ELSE NULL
END,
CASE @field2
WHEN 'Name' THEN CASE @direction2 WHEN 'A' THEN name_order ELSE -name_order END
WHEN 'Age' THEN CASE @direction2 WHEN 'A' THEN age_order ELSE -age_order END
ELSE NULL
END
重复多次要求...
注意:只需因为可以这样做,并不意味着它应该这样做。
Here is the solution
Explanation:
1. Ordering the row number on the basis of SQL input param order by (DESC, ASC)
2. Againt ordering the row number in outer query
Try this code (working)
DECLARE @PageNum int
DECLARE @PageSize int
DECLARE @TotalRowsNum int
DECLARE @SortColumn varchar(200)
DECLARE @SortOrder varchar(5)
SET @PageNum = 4;
SET @PageSize = 10;
SET @SortColumn = 'CODE_ID';
SET @SortOrder = 'DESC';
WITH QueryResult AS
(
SELECT *,
CASE @SortOrder WHEN 'ASC' THEN
ROW_NUMBER() OVER(ORDER BY @SortColumn ASC)
ELSE
ROW_NUMBER() OVER(ORDER BY @SortColumn DESC)
END AS 'RowNumber'
FROM TABLE_NAME
)
SELECT * FROM QueryResult
WHERE RowNumber BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize
ORDER BY RowNumber ASC
如果您要为您的问题添加更多详细信息,请尝试按照我们的答案不会因此而无关紧要的方式进行操作。如果你看到可能的话,最好发布一个新问题,如果需要的话可能参考这个问题。 – 2011-02-14 15:43:59