我在查询订单;
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.99My 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.99Can 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;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.99My 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.99Can 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;
发布评论