在SELECT语句中对两列包含不同值的列进行分组

I have a SQL query I am performing against some tables SQL Server database. The result set looks something like this:

我有一个SQL查询我正在对一些表SQL Server数据库执行。结果集看起来像这样:

Column1 | Column2 | Column3 | InvoiceNumber | InvoiceID
-------------------------------------------------------
1        1         1          1234            1
1        1         1          5678            2

I only want to get back the row with the smallest InvoiceID and its corresponding InvoiceNumber.

我只想用最小的InvoiceID及其相应的InvoiceNumber返回行。

I apply min(InvoiceID) in the SELECT statement. In the GROUP BY clause, I am grouping by every column except InvoiceNumber and InvoiceID. I am getting an error since InvoiceNumber isn't contained in an aggregate function in the SELECT statement and isn't present in the GROUP BY clause.

我在SELECT语句中应用min(InvoiceID)。在GROUP BY子句中,我按除InvoiceNumber和InvoiceID之外的每个列进行分组。我收到错误,因为InvoiceNumber不包含在SELECT语句中的聚合函数中,并且不存在于GROUP BY子句中。

How do I structure the query so I only get back the row with the smallest InvoiceID and the corresponding InvoiceNumber?

如何构造查询,以便我只返回具有最小InvoiceID和相应InvoiceNumber的行?

Edit

Here is some additional information:

以下是一些其他信息:

The result set is actually going to contain many rows. It's only occassionally that multiple rows will have the same values for Column1, Column2, and Column3, but different values for the InvoiceNumber and InvoiceID. So a better example of the result set would look like this:

结果集实际上将包含许多行。只是偶尔会有多行对Column1,Column2和Column3具有相同的值,但InvoiceNumber和InvoiceID的值不同。因此,结果集的更好示例如下所示:

Column1 | Column2 | Column3 | InvoiceNumber | InvoiceID
-------------------------------------------------------
1        1         1          1234            1
1        1         1          5678            2
2        1         1          1287            3
2        1         2          1287            3
2        2         1          1287            3
3        1         1          5555            4
3        1         2          5555            4

So when Column1, Column2, and Column3 are the same, I only want the result with the smallest InvoiceID, and whatever the InvoiceNumber was for the row with the smallest InvoiceID. Here's what I want it to actually look like in this case:

因此,当Column1,Column2和Column3相同时,我只想要具有最小InvoiceID的结果,以及具有最小InvoiceID的行的InvoiceNumber。以下是我希望它在这种情况下实际看起来像:

Column1 | Column2 | Column3 | InvoiceNumber | InvoiceID
-------------------------------------------------------
1        1         1          1234            1
2        1         1          1287            3
2        1         2          1287            3
2        2         1          1287            3
3        1         1          5555            4
3        1         2          5555            4

Only the second row was removed because it had a smaller InvoiceID and was the only row that had the same values for Column1, Column2, and Column3 as a different row.

仅删除了第二行,因为它具有较小的InvoiceID,并且是Column1,Column2和Column3具有相同值的唯一行作为不同的行。

Here's what my SQL query currently looks like:

这是我的SQL查询目前的样子:

SELECT c.CollectionID
    , p.Account
    , d.TransactionID
    , id.InvoiceNumber
    , i.InvoiceID
FROM collection c
INNER JOIN documents d on c.GlobalCollectionID = d.GlobalCollectionID
LEFT JOIN payment p on c.GlobalCollectionID = p.GlobalCollectionID
    AND d.TransactionID = p.TransactionID
LEFT JOIN invoice i on c.GlobalCollectionID = i.GlobalCollectionID
    AND d.TransactionID = i.TransactionID
LEFT JOIN invoicedata id on i.InvoiceID = id.InvoiceID
WHERE c.ProcessDate = '2013-11-29'
    AND c.Item = 11805123
    AND c.CollectionID in ('1104', '1105', '1106')
ORDER BY c.CollectionID
    , d.TransactionID
    , i.InvoiceID
    , p.Account

3 个解决方案

#1

It would probably be easier to help if you'd posted the SQL to your initial query.

如果您将SQL发布到初始查询中,可能会更容易提供帮助。

You probably want to move your MIN() function into a WHERE clause:

您可能希望将MIN()函数移动到WHERE子句中:

SELECT Column1 ,Column2 ,Column3 ,InvoiceNumber , InvoiceID
FROM Table_Name
WHERE InvoiceID = (SELECT MIN(InvoiceID) FROM Table_Name)

#2

Now you have provided some data and have changed your requirement as well anyway with this new data and new requirement , try this

现在您提供了一些数据,并且无论如何都要根据这些新数据和新要求更改您的要求,请尝试此操作

DECLARE @T Table (Column1 INT,Column2 INT,Column3 INT,InvoiceNumber INT, InvoiceID INT)

INSERT INTO @T
VALUES
(1,1,1,1234,1),
(1,1,1,5678,2),
(2,1,1,1287,3),
(2,1,2,1287,3),
(2,2,1,1287,3),
(3,1,1,5555,4),
(3,1,2,5555,4)

;WITH CTE
 AS
   (
   SELECT Column1 ,Column2 
          ,Column3 ,InvoiceNumber , InvoiceID 
          , rn = ROW_NUMBER() OVER (PARTITION BY Column1 ,Column2 ,Column3  ORDER BY InvoiceID ASC)
   FROM @T
   )
 SELECT * FROM CTE
 WHERE rn = 1

Result Set

Column1 Column2 Column3 InvoiceNumber   InvoiceID   rn
   1       1       1    1234               1        1
   2       1       1    1287               3        1
   2       1       2    1287               3        1
   2       2       1    1287               3        1
   3       1       1    5555               4        1
   3       1       2    5555               4        1

Your Query

;WITH CTE
 AS
   (
    SELECT YourQuery.* 
    , rn = ROW_NUMBER() OVER (PARTITION BY c.CollectionID , p.Account , d.TransactionID ORDER BY InvoiceID ASC)
    ( 
     SELECT c.CollectionID
        , p.Account
        , d.TransactionID
        , id.InvoiceNumber
        , i.InvoiceID
    FROM collection c
    INNER JOIN documents d on c.GlobalCollectionID = d.GlobalCollectionID
    LEFT JOIN payment p on c.GlobalCollectionID = p.GlobalCollectionID
        AND d.TransactionID = p.TransactionID
    LEFT JOIN invoice i on c.GlobalCollectionID = i.GlobalCollectionID
        AND d.TransactionID = i.TransactionID
    LEFT JOIN invoicedata id on i.InvoiceID = id.InvoiceID
    WHERE c.ProcessDate = '2013-11-29'
        AND c.Item = 11805123
        AND c.CollectionID in ('1104', '1105', '1106')
      )YourQuery
   )
 SELECT C.CollectionID , C.Account , C.TransactionID,C.InvoiceNumber, C.InvoiceID 
 FROM CTE C
 WHERE rn = 1

#3

This is the query you're looking for:

这是您正在寻找的查询:

SELECT Column1, Column2, Column3, InvoiceNumber, InvoiceID
FROM ( 
    SELECT *,
        rn = ROW_NUMBER() OVER (PARTITION BY Column1, Column2, Column3
                                ORDER BY InvoiceID ASC)
    FROM example
) e
WHERE rn = 1

See it working on SQLFiddle. Just swap in your query for the nested query, but keep the ROW_NUMBER field.

看到它在SQLFiddle上工作。只需在查询中交换嵌套查询,但保留ROW_NUMBER字段。

Produces the correct result set:

生成正确的结果集:

COLUMN1  COLUMN2  COLUMN3  INVOICENUMBER   INVOICEID
----------------------------------------------------
1        1        1        1234            1
2        1        1        1287            3
2        1        2        1287            3
2        2        1        1287            3
3        1        1        5555            4
3        1        2        5555            4