如何查询查询结果？

I created query to fetch top selling products. Now, I want to query this result, for example, top 100 products order by SerialNo or top 100 products order by SellingQty... etc..

我创建了查询以获取畅销产品。现在，我想查询这个结果，例如，按SerialNo排序的前100个产品或SellingQty排序的前100个产品......等等。

Here is my query which give all products order by totalOrders :

这是我的查询，它通过totalOrders提供所有产品订单：

    SELECT tblProducts.skuCode,tblProducts.productCode, tblProducts.productName, COUNT(tblOrders_Products.delivered) as totalOrder
    FROM tblOrders_Products INNER JOIN tblProducts ON tblOrders_Products.productID = tblProducts.productID
    WHERE tblProducts.productName is not null
    GROUP BY tblOrders_Products.delivered, tblOrders_Products.productID, tblProducts.skuCode,     tblProducts.productName,tblProducts.productCode
    ORDER BY totalOrder

I considered to create a view. But many posts are telling that, using Order By clause with view, is harmful or not give result sometime, So, I am wondering how this can be done?

我考虑过创建一个视图。但很多帖子都说，使用带有视图的Order By子句，有时是有害的还是不给出结果，所以，我想知道如何做到这一点？

I can do this in .NET by sorting DataTable, and using that as DataSource. But how it can be done in SQL SERVER?

我可以通过对DataTable进行排序并将其用作DataSource来在.NET中执行此操作。但是如何在SQL SERVER中完成它？

And which way is faster? Ordering in SQL Server or in DataTable? I am using SQL SERVER 2005.

哪条路更快？在SQL Server或DataTable中订购？我正在使用SQL SERVER 2005。

Thanks.

谢谢。

2 个解决方案

#1

This should give you the Top 100 products ordered by SerialNo (I assumed it was a part of tblProducts here).

这应该为您提供SerialNo订购的前100个产品（我认为它是tblProducts的一部分）。

You can switch the Order By parameter to get it ordered the other fields.

您可以切换Order By参数以使其按其他字段排序。

SELECT a.skuCode, a.productCode, a.productName, a.totalOrder, a.SerialNo
FROM
(
SELECT TOP 100 tblProducts.skuCode,tblProducts.productCode, tblProducts.productName, COUNT(tblOrders_Products.delivered) as totalOrder, tblProducts.SerialNo
    FROM tblOrders_Products INNER JOIN tblProducts ON tblOrders_Products.productID = tblProducts.productID
    WHERE tblProducts.productName is not null
    GROUP BY tblOrders_Products.delivered, tblOrders_Products.productID, tblProducts.skuCode,     tblProducts.productName,tblProducts.productCode
    ORDER BY totalOrder) a
ORDER BY a.SerialNo

#2

Something like this should do it:

这样的事情应该这样做：

WITH TopProducts AS
(
    SELECT TOP 100 tblProducts.productID, COUNT(tblOrders_Products.delivered) as totalOrder
    FROM tblOrders_Products INNER JOIN tblProducts ON tblOrders_Products.productID = tblProducts.productID
    WHERE tblProducts.productName is not null
    GROUP BY tblProducts.productID 
    ORDER BY COUNT(tblOrders_Products.delivered) DESC
)
SELECT * 
FROM TopProducts INNER JOIN tblProducts ON TopProducts.productID = tblProducts.productID
ORDER BY tblProducts.SerialNo

The TopProducts is called a Common Table Expression in SQL Server, and it's a neat way to reuse query parts.

TopProducts在SQL Server中称为公用表表达式，它是重用查询部件的一种巧妙方法。

Sorting a DataTable in .NET will woks as well. You will probably not notice any difference in performance for only 100 rows. Sorting on the client would actually be preferrable in a scenario when the user may want to sort the results in different ways, e.g. by clicking column headers in a grid, since this can be accomplished without a separate database call.

在.NET中对DataTable进行排序也很有意思。您可能不会发现只有100行的性能差异。在用户可能想要以不同方式对结果进行排序的情况下，客户端上的排序实际上是优选的，例如，通过单击网格中的列标题，因为这可以在没有单独的数据库调用的情况下完成。

#1