SQL Server - GROUP BY在一列上 [英] SQL Server - GROUP BY on one column

问题描述

我试图从订单查看订单。在我看来，我确实有一些具有完全相同值的行，但我希望将这些值分组到orderid并获取该订单数量的总和。

我的查看结果如下所示：

  Order_id Customer_id Article_id Delivery_date Quantity 
 ------------ -------------------------------------------------- ------------- 
 PR10.001 11 20.001a 17-04-2013 1 
 PR10.001 11 20.001a 17-04-2013 1 
 PR10 .001 11 20.001a 17-04-2013 1 
 PR13.001 15 41.022b 19-04-2013 1 
 PR13.001 15 41.022b 19-04-2013 1 
  

我想要这样做：

  SELECT Order_id，Customer_id Article_id，Delivery_date，sum（Quantity）
 FROM Orders 
 GROUP BY Order_id 
  

获取类似的信息：

  Order_id Customer_id Arti cle_id Delivery_date Quantity 
 ------------------------------------------- -------------------------------- 
 PR10.001 11 20.001a 17-04-2013 3 
 PR13.001 15 41.022b 19-04-2013 2 
  

但我知道通过一个单独的分组列是不可能的，否则你会得到消息：

[...]在选择列表中无效，因为它不是包含在
集合函数或GROUP BY子句中。

是否有另一种可能性或解决方法SQL Server中的列？

解决方案

您可以使用 CTE 与 SUM（Quantity）OVER（PARTITION BY Order_id） +













< （选择order_id，
customer_id，
article_id，
delivery_date，
quantity = Sum（数量））pre $ WITH cte
AS
OVER（
分区BY order_id），
rn = Row_number（）
OVER（
分区BY order_id
ORDER BY delivery_date ASC）
FROM orders）
SELECT order_id，
customer_id，$ b $ article_id，
delivery_date，
quantity
FROM cte
WHERE rn = 1


DEMO

然而，您想要的结果似乎不正确

这是我的结果：

  ORDER_ID CUSTOMER_ID ARTICLE_ID DELIVERY_DATE QUANTITY 
 PR10.001 11 20.001a 17-04-2013 3 
 PR13.001 15 41.022b 19-04-2013 2 
  

I'm trying to get orders from an orderview. In my view I do have some rows with exactly the same values, but I want to group these values on orderid and take the sum of the quantity of that order.

My view results something like:

 Order_id   Customer_id   Article_id   Delivery_date   Quantity
 ---------------------------------------------------------------------------
 PR10.001   11            20.001a      17-04-2013      1
 PR10.001   11            20.001a      17-04-2013      1
 PR10.001   11            20.001a      17-04-2013      1
 PR13.001   15            41.022b      19-04-2013      1
 PR13.001   15            41.022b      19-04-2013      1

I want to do something like:

SELECT Order_id, Customer_id Article_id, Delivery_date, sum(Quantity)
FROM Orders
GROUP BY Order_id

To get something like:

 Order_id   Customer_id   Article_id   Delivery_date   Quantity
 ---------------------------------------------------------------------------
 PR10.001   11            20.001a      17-04-2013      3
 PR13.001   15            41.022b      19-04-2013      2

But I know grouping by one single column is not possible, otherwise you'll get the message:

[...] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there another possibility or workaround to group by one specific column in SQL Server?

解决方案

You could use a CTE with SUM(Quantity)OVER(PARTITION BY Order_id) + ROW_NUMBER to pick out the desired row from the order-group:

WITH cte 
     AS (SELECT order_id, 
                customer_id, 
                article_id, 
                delivery_date, 
                quantity=Sum(quantity) 
                           OVER( 
                             partition BY order_id), 
                rn = Row_number() 
                       OVER( 
                         partition BY order_id 
                         ORDER BY delivery_date ASC) 
         FROM   orders) 
SELECT order_id, 
       customer_id, 
       article_id, 
       delivery_date, 
       quantity 
FROM   cte 
WHERE  rn = 1 

DEMO

However, your desired result seems to be incorrect (question edited)

This is my result:

ORDER_ID    CUSTOMER_ID ARTICLE_ID  DELIVERY_DATE   QUANTITY
PR10.001    11          20.001a         17-04-2013  3
PR13.001    15          41.022b         19-04-2013  2

这篇关于SQL Server - GROUP BY在一列上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！