如何在SQL Server的另一列中以最高的值为每个客户获取单个行?

I have a table that contains some customer information:

我有一个包含一些客户信息的表格:

--------------------------------------------
|CustID  CustType  OrderType  CountofOrders|
|------------------------------------------|
|  1        C         DD          23       |
|  2       IC         DE          10       |
|  2       IC         DR           7       |
|  2       IC         DS           7       |
|  3        C         DR          14       |
|  3        C         DS          19       |
|  4       IC         DS           5       |
|  4       IC         DR           5       |
|  4       IC         DE           3       |
|  5       IC         DR           7       |
|  5       IC         DS           7       |
|  5       IC         DE           7       |
--------------------------------------------

I want to pull all records for Customers when CustType is not 'IC', regardless of how many rows they have.

当CustType不是“IC”时，我想为客户拉出所有记录，不管它们有多少行。

So for instance, CustID 1 would return 1 row while CustID 3 would return 2 rows. When the CustType is 'IC', I only want to pull a single row for that customer based on the highest CountofOrders.

例如，CustID 1返回一行，CustID 3返回2行。当CustType是“IC”时，我只希望根据最高的CountofOrders为该客户拉出一行。

So for CustID 2, it should pull the record with OrderType 'DE' and CountofOrders as 10.

所以对于CustID 2，它应该使用OrderType 'DE'和CountofOrders作为10来提取记录。

If the 'IC' customer has the same count for the various OrderTypes, then I want to get the row where the OrderType is 'DE'. If the OrderType is not 'DE', then I want to get the row with the OrderType as 'DS'. In short, priority should be given to OrderType 'DE', followed by 'DS', 'DR' and 'DD'

如果“IC”客户对不同的OrderType有相同的计数，那么我想获得OrderType为'DE'的行。如果OrderType不是'DE'，那么我想获得将OrderType作为'DS'的行。简而言之，应该优先考虑OrderType 'DE'，然后是'DS'， 'DR'和'DD'

So my final output should look something like this:

最终输出应该是这样的

-------------------------------------------
|CustID  CustType  OrderType  CountofOrders|
-------------------------------------------|
|  1        C         DD           23      |
|  2       IC         DE           10      |
|  3        C         DR           14      |
|  3        C         DS           19      |
|  4       IC         DS            5      |
|  5       IC         DE            7      |
--------------------------------------------

I was able to use the following query to get the highest for when the CountofOrders is not the same, but I'm having a hard time figuring out how to get a row based on the OrderType when the CountofOrders is the same between the different OrderTypes.

当CountofOrders不是相同的时候，我可以使用下面的查询来获取最高的值，但是当不同的OrderTypes之间的CountofOrders是相同的时，我很难弄清楚如何根据OrderType获得行。

SELECT 
    a.CustID, a.CustType, a.OrderType, a.CountofOrders
FROM 
    CustInfo a 
WHERE 
    a.CountofOrders = (SELECT MAX(CountofOrders) 
                       FROM CustInfo b 
                       WHERE a.CustID = b.CustID)

Any help would be greatly appreciated. Thanks.

如有任何帮助，我们将不胜感激。谢谢。

2 个解决方案

#1

You can do this using ROW_NUMBER pretty easily. This returns the results you stated you wanted from your sample data.

使用ROW_NUMBER可以很容易地做到这一点。这将返回您希望从示例数据中得到的结果。

DECLARE @Customer table
(
    CustID int
    , CustType varchar(10)
    , OrderType char(2)
    , CountofOrders int
)

INSERT @Customer
(
    CustID
    , CustType
    , OrderType
    , CountofOrders
)
VALUES
(1, 'C', 'DD', 23)
, (2, 'IC', 'DE', 10)
, (2, 'IC', 'DR', 7)
, (2, 'IC', 'DS', 7)
, (3, 'C', 'DR', 14)
, (3, 'C', 'DS', 19)
, (4, 'IC', 'DS', 5)
, (4, 'IC', 'DR', 5)
, (4, 'IC', 'DE', 3)
, (5, 'IC', 'DR', 7)
, (5, 'IC', 'DS', 7)
, (5, 'IC', 'DE', 7)

SELECT x.CustID
    , x.CustType
    , x.OrderType
    , x.CountofOrders
FROM
(
    SELECT *
         , ROW_NUMBER() over(partition by CustID order by CountOfOrders desc
            , case OrderType 
                when 'DE' then 1
                when 'DS' then 2
                when 'DR' then 3
                when 'DD' then 4
                else 5
            end) as RowNum
    FROM @Customer
) x
WHERE x.CustType <> 'IC'
    OR x.RowNum = 1
ORDER BY CustID
    , CountofOrders

#2

I think your requirement cannot be done in query level because the order is based on priority. I mean you have to use something like stored procedure or do the ordering process in your application

我认为您的需求不能在查询级别完成，因为订单是基于优先级的。我的意思是，您必须在应用程序中使用存储过程或排序过程之类的东西

#1