SQL Server：针对具有多个行项目的订单仅输出一个邮资成本(SQL Server : output only one Postage Cost Against an Order with Mul

SQL Server：针对具有多个行项目的订单仅输出一个邮资成本(SQL Server : output only one Postage Cost Against an Order with Multiple Line Items)

我在查询订单;

Order# Customer Item Postage 11111 Customer1 Item1 £3.99 11112 Customer2 Item1 £3.99 11112 Customer2 Item2 £3.99 11112 Customer2 Item3 £3.99

我的问题是每个订单我只想输出一个邮费。 在上面的例子中，11112输出三个邮资费用。

期望的结果;

Order# Customer Item Postage 11111 Customer1 Item1 £3.99 11112 Customer2 Item1 11112 Customer2 Item2 11112 Customer2 Item3 £3.99

任何人都可以指导我一些文档或提供样本SELECT查询来处理这个问题吗？

我简化了问题和输出，对于那些想要查看实际数据库结构的人，我附上了订单表（Linnworks）的图像，忽略了与该问题无关的邮政服务表。

I am querying orders;

Order# Customer Item Postage 11111 Customer1 Item1 £3.99 11112 Customer2 Item1 £3.99 11112 Customer2 Item2 £3.99 11112 Customer2 Item3 £3.99

My problem is for each order I only want to output one postage cost. In the above example, 11112 is outputting three postage costs.

Desired result;

Order# Customer Item Postage 11111 Customer1 Item1 £3.99 11112 Customer2 Item1 11112 Customer2 Item2 11112 Customer2 Item3 £3.99

Can anyone direct me to some documentation or provide from sample SELECT queries for dealing with this problem?

I have simplified the question and output, for those that would like to look at the actual Database structure, I have attached an image of the order table (Linnworks), ignore the Postal Services table it has no relevance to the question.

最满意答案

我认为你可以用row_number()做你想做的事：

with q as ( <your query here> ) select OrderNum, Customer, Item, (case when seqnum = 1 then postage end) as Postage from (select q.*, row_number() over (partition by ordernum order by (select null)) as seqnum from q ) q;

I think you can do what you want with row_number():

with q as ( <your query here> ) select OrderNum, Customer, Item, (case when seqnum = 1 then postage end) as Postage from (select q.*, row_number() over (partition by ordernum order by (select null)) as seqnum from q ) q;