SQL中的条件公用表表达式(CTE)
问题描述:
我试图选择SQL中产品类别树的层次结构。SQL中的条件公用表表达式(CTE)
我的代码如下所示。我试图实现动态排序顺序,在SortOrder参数上使用IF或Case When。
如果@SortOrder等于'sortorder',则注释行应该是活动的。我试图加上If Else的陈述,但我失败了......
你能帮忙吗?
CREATE PROCEDURE [dbo].[ProductCategory_SelectHierarchy]
@SortOrder varchar(30)
AS
SET NOCOUNT ON;
WITH Categories (Id,ParentId,SortOrder,RowOrder) as
(
SELECT parentCategory.Id,
parentCategory.ParentId,
parentCategory.SortOrder,
--cast(REPLACE(STR(parentCategory.SortOrder, 8), ' ', '0') as varchar(30)) 'RowOrder'
cast(CAST(DATEPART(YEAR, parentCategory.DateCreated) as varchar(4)) +
CAST(DATEPART(MONTH, parentCategory.DateCreated) as varchar(2)) +
CAST(DATEPART(DD, parentCategory.DateCreated) as varchar(2)) +
CAST(DATEPART(HOUR, parentCategory.DateCreated) as varchar(2)) as varchar(50)) 'RowOrder'
FROM ProductCategories parentCategory
WHERE ParentId = 0
UNION ALL
SELECT childCategories.Id,
childCategories.ParentId,
childCategories.SortOrder,
--cast(Categories.RowOrder + REPLACE(STR(childCategories.SortOrder, 8), ' ', '0') as varchar(30)) 'RowOrder'
cast(Categories.RowOrder + '/' + CAST(DATEPART(YEAR, childCategories.DateCreated) as varchar(4)) +
CAST(DATEPART(MONTH, childCategories.DateCreated) as varchar(2)) +
CAST(DATEPART(DD, childCategories.DateCreated) as varchar(2)) +
CAST(DATEPART(HOUR, childCategories.DateCreated) as varchar(2)) as varchar(50)) 'RowOrder'
FROM ProductCategories childCategories
JOIN Categories
ON childCategories.ParentId = Categories.Id
)
SELECT pc.*, Categories.RowOrder
FROM Categories
INNER JOIN ProductCategories pc ON pc.Id = Categories.Id
ORDER BY RowOrder
答
您应该能够像这样排序的:
ORDER BY
CASE
WHEN @SortOrder = 'date_column' THEN CONVERT(VARCHAR(20), date_column, 120)
WHEN @SortOrder = 'customer_id' THEN RIGHT(REPLICATE('0', 20) + CAST(customer_id AS VARCHAR(20)), 20)
WHEN @SortOrder = 'name' THEN name
ELSE sort_order
END
的关键是让所有排序的列(或表达式)的最后形成相同的数据类型。
无法正常工作。 虽然,你的伪代码不会做我想要实现的。 我希望@SortOrder可以像'createdate','salescount','name'或'custom'一样更静态。 每种情况都应该导致不同的SQL语句。 – MartinHN 2010-06-14 17:14:47
我刚刚编辑了答案,现在我对您要查找的内容有了更清晰的了解。 – 2010-06-14 17:23:22
哦,我应该按照从句的顺序来做。 我改变了我的代码,所有4列添加到数据集,这样做是为了通过: ORDER BY CASE \t WHEN @SortOrder = 'NameSortedOrder',那么Categories.NameSortedOrder \t WHEN @SortOrder =“SalesCountOrder 'THEN Categories.SalesCountOrder \t WHEN @SortOrder ='DateCreatedOrder'THEN Categories.DateCreatedOrder \t ELSE Categories.SortOrder END – MartinHN 2010-06-14 18:35:39