按特定顺序进行列分组

发布于 2024-09-03 07:55:44 字数 956 浏览 3 评论 0原文

是否可以使用 GROUP BY 更改查询结果集中记录/组的顺序？

我有这样的查询：

SELECT Category,
    Subcategory,
    ProductName,
    CreatedDate,
    Sales
FROM TableCategory tc INNER JOIN
    TableSubCategory ts ON tc.col1 = ts.col2 INNER JOIN
    TableProductName tp ON ts.col2 = tp.col3   
GROUP BY Category,
    SubCategory,
    ProductName,
    CreatedDate,
    Sales

现在，我正在创建一个 SSRS 报告，其中 Category 是主行组，然后 SubCategory 是其子行组。那么 ProductName 是一个主列组。

[屏幕截图的损坏链接：http://www.(freeimagehosting).net/uploads /8035123725.jpg]

它工作完美，但它按字母顺序显示 ProductNames。我希望它以自定义顺序（由我定义）显示 ProductNames，例如：

ProductNo5 in 3rd column,  
ProductNo8 in 4th column,  
ProductNo1 in 5th column   
... and so on!

原文

Is it possible to change order of records/groups in a result-set from a query using GROUP BY?

I have this query:

SELECT Category,
    Subcategory,
    ProductName,
    CreatedDate,
    Sales
FROM TableCategory tc INNER JOIN
    TableSubCategory ts ON tc.col1 = ts.col2 INNER JOIN
    TableProductName tp ON ts.col2 = tp.col3   
GROUP BY Category,
    SubCategory,
    ProductName,
    CreatedDate,
    Sales

Now, I am creating a SSRS report where Category is Primary row group, then SubCategory is its child row group. Then ProductName is a Primary Column Group.

[Broken link to screenshot: http://www.(freeimagehosting).net/uploads/8035123725.jpg]

It works perfect, but it shows the ProductNames in alphabatic order. I want it to show the ProductNames in custom order (defined by me) like:

ProductNo5 in 3rd column,  
ProductNo8 in 4th column,  
ProductNo1 in 5th column   
... and so on!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

Oo萌小芽oO 2024-09-10 07:55:44

我不知道产品 ssrs，但我知道通用 SQL。在 SQL 中，您必须添加额外的列。您可以将 Numeric/number 类型的列 myOrder 添加到 ProductNames 表并填充 myOrder 列，以便它对应于您想要的任何排序。之后你可以做类似的事情：

SELECT Category, 
    Subcategory, 
    ProductName, 
    CreatedDate, 
    Sales 
FROM TableCategory tc 
INNER JOIN TableSubCategory ts 
ON tc.col1 = ts.col2 
INNER JOIN TableProductName tp 
ON ts.col2 = tp.col3
GROUP BY Category, 
    SubCategory, 
    ProductName, 
    CreatedDate, 
    Sales 
ORDER BY TableProductName.myOrder

I don't know the product ssrs, but I do know general SQL. In SQL you'd have to add an extra column. You could add a column myOrder of type Numeric/number to the ProductNames table and fill the myOrder column so it corresponds to whatever ordering you want. After that you could do something like that:

SELECT Category, 
    Subcategory, 
    ProductName, 
    CreatedDate, 
    Sales 
FROM TableCategory tc 
INNER JOIN TableSubCategory ts 
ON tc.col1 = ts.col2 
INNER JOIN TableProductName tp 
ON ts.col2 = tp.col3
GROUP BY Category, 
    SubCategory, 
    ProductName, 
    CreatedDate, 
    Sales 
ORDER BY TableProductName.myOrder

回复收藏 0 原文

~没有更多了~