SQL Server 2012组仅由一列组成

I need to group only by one column

我只需要分组一列

SQL Server 2012 query:

SQL Server 2012查询：

SELECT OrderID,Status,DateEdited 
FROM orders 
WHERE Status='Pending' 
GROUP BY OrderID,Status,DateEdited

and the result is:

结果是：

As you can see there are duplicate OrderId column values.

如您所见，有重复的OrderId列值。

Works but it groups by OrderId,Status,DateEdit but what I need is that the OrderId would be unique in the results can I have something like:

可以工作，但它按OrderId，Status，DateEdit进行分组，但我需要的是OrderId在结果中是唯一的，我可以这样：

SELECT OrderID,Status,DateEdited 
FROM orders 
WHERE Status='Pending' 
GROUP BY OrderID

2 个解决方案

#1

You have to use an aggregate function for status and DateEdited, to get distinct valeus for each grouped ORDERID. You can also use the ranking functions to do so like this:

您必须使用状态和DateEdited的聚合函数来为每个分组的ORDERID获取不同的valeus。你也可以使用排名函数这样做：

WITH CTE
AS
(
   SELECT 
     OrderID,
     Status,
     DateEdited,
     ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY DateEdited DESC) rownum
   FROM orders 
   WHERE Status='Pending'
)
SELECT 
  OrderID,
  Status,
  DateEdited
FROM CTE
WHERE rownum = 1;

This will give you distinct ORDERIDs. But which status and DateEdited to return for each grouped ORDERID?

这将为您提供不同的ORDERID。但是为每个分组的ORDERID返回哪个状态和DateEdited？

#2

Then you can do like this,

然后你可以这样做，

SELECT OrderID,'Pending' as Status,max(DateEdited) 
FROM orders 
WHERE Status='Pending' 
GROUP BY OrderID

If you dont want to loose any record then you can go for GROUP_CONCAT()

如果你不想丢失任何记录，那么你可以去GROUP_CONCAT（）

SELECT OrderID,'Pending' as Status,GROUP_CONCAT(DateEdited) 
FROM orders 
WHERE Status='Pending' 
GROUP BY OrderID

Note: Am not sure whether you GROUP_CONCAT in sqlserver Incase it's not there go for a function like that. :)

注意：我不确定你是否在sqlserver中使用GROUP_CONCAT包含它不适用于那样的功能。 :)