如何从列中清除冗余值？

This is the sample output 如何从列中清除冗余值？

这是示例输出

Let me explain what's going on:

让我解释一下发生了什么：

The query returns all invoices # of every year along with the products that is involved in the invoice.

查询将返回每年的所有发票号以及发票中涉及的产品。

As you see, we have two invoice in 2010...The invoices are 30463 and 30516. The invoice 30463 has 4 products, its shipping price is 105.88. As you see the shipping price is repeated on every product which causes troubles when i calculate sum at reporting level. The 4 products of invoice # 30463 has shipping price of 105.00 overall. I want every shipping price of every invoice to be shown only once regardless how many products within an invoice. How can i achieve it?

如您所见，我们在2010年有两张发票......发票是30463和30516.发票30463有4个产品，运费价格是105.88。正如您所看到的那样，当我在报告级别计算总和时，会对每件产品重复运输价格，这会导致麻烦。发票＃30463的4个产品的总运费为105.00。我希望每张发票的每个运费价格只显示一次，无论发票中有多少产品。我怎样才能实现它？

HERE IS THE QUERY:

这是查询：

SELECT 
      DATEPART(year, CustomerInvDetail.sentDate) AS "Year", 
      CustomerInvoice.cuInvoiceID,  
      Product.productName, 
      CustomerQuoteProducts.unitPrice, 
      CustomerQuoteProducts.qty, 
      CustomerQuoteProducts.qty * CustomerQuoteProducts.unitPrice AS "Price",
      CustomerShipping.shippingPrice
FROM  CustomerInvoice INNER JOIN CustomerInvDetail 
      ON CustomerInvoice.cuInvoiceID = CustomerInvDetail.cuInvoiceID
      INNER JOIN CustomerQuote 
      ON CustomerQuote.customerQuoteID = CustomerInvoice.customerQuoteID
      INNER JOIN CustomerQuoteProducts 
      ON CustomerQuoteProducts.customerQuoteID = CustomerQuote.customerQuoteID
      INNER JOIN CustomerShipping 
      ON CustomerShipping.customerQuoteID = CustomerInvoice.customerQuoteID
      INNER JOIN Customer 
      ON Customer.customerID = CustomerQuote.customerID
      INNER JOIN Product 
      ON CustomerQuoteProducts.productID = Product.productID
WHERE (DATEPART(year, CustomerInvDetail.sentDate) BETWEEN 2001 AND 2022) AND (Customer.customerID = 500)

2 个解决方案

#1

Something along these lines maybe?

沿着这些方向可能有什么？

case when row_number() over(partition by cuInvoiceId order by newid()) = 1 then shippingPrice end

Update

更新

What it does is this:

它的作用是：

It divides the data onto partitions depending on the cuInvoiceId value
它根据cuInvoiceId值将数据划分到分区上
Now, inside this partition we want to enumerate every row but there's nothing to anchor to so I used newid() which basically means enumerate those rows randomly.
现在，在这个分区里面我们要枚举每一行，但没有什么可以锚定，所以我使用了newid（），这基本上意味着随机枚举这些行。
And, finally, with case ... = 1 I want the very first row to be the one that would display shippingPrice and all others -- null.
最后，对于case ... = 1我希望第一行是显示shippingPrice和所有其他行的那一行 - null。

#2

How about a case statement on the shipping price when it's the first item? I'm assuming you have a lineitem or some way to determining the first item on an invoice - then

如果它是第一个项目的运费价格的案例声明怎么样？我假设你有一个lineitem或某种方式确定发票上的第一项 - 然后

case when lineno = 1 then CustomerShipping.shippingPrice else 0 end

当lineno = 1然后CustomerShipping.shippingPrice else 0结束时的情况

#1