聚合SQL数据- MS SQL server 2008

I have some trouble with grouping the data by the order number and aggregating the "support columns" while selecting always the "highest" value.

我在按照订单号对数据进行分组并在始终选择“最高”值的同时聚合“支持列”时遇到了一些麻烦。

╔═════════════╦══════════════╦════════════════╗
║ OrderNumber ║ PhoneSupport ║ ServiceSupport ║
╠═════════════╬══════════════╬════════════════╣
║ 0000000001  ║ 0020         ║                ║
║ 0000000001  ║ 0010         ║ 0030           ║
║ 0000000001  ║ 0010         ║ 0020           ║
║ 0000000002  ║              ║ 0030           ║
║ 0000000002  ║ 0030         ║                ║
║ 0000000003  ║ 0020         ║                ║
║ 0000000003  ║ 0030         ║                ║
╚═════════════╩══════════════╩════════════════╝

In this example the output should be like this.

在本例中，输出应该是这样的。

╔═════════════╦══════════════╦════════════════╗
║ OrderNumber ║ PhoneSupport ║ ServiceSupport ║
╠═════════════╬══════════════╬════════════════╣
║ 0000000001  ║ 0020         ║ 0030           ║
║ 0000000002  ║ 0030         ║ 0030           ║
║ 0000000003  ║ 0030         ║                ║
╚═════════════╩══════════════╩════════════════╝

So far I have often read in various forums something about cursors but I don't like to use it.

到目前为止，我经常在各种论坛上阅读关于游标的内容，但我不喜欢使用它。

My idea was to use the over clause but I am not sure if it is a solution for that case.

我的想法是使用over子句，但我不确定它是否是那种情况的解决方案。

2 个解决方案

#1

Use GROUP BY, do MAX on the columns you want the "highest" value for.

使用GROUP BY，在您想要的“最高”值的列上执行MAX。

select OrderNumber, max(PhoneSupport), max(ServiceSupport)
from tablename
group by OrderNumber

#2

You can use Group By and Max and then wrap the query in parent to do Order By on aggregate columns. Example shown below.

您可以使用Group By和Max，然后将查询封装在父类中，在聚合列上按顺序执行。例子所示。

select * 
from (
    select OrderNumber, max(PhoneSupport) as PhoneSupport, max(ServiceSupport)  as ServiceSupport
    from tablename
    group by OrderNumber) aa
Order By aa.PhoneSupport

#1